package com.szholly.pro.watergas_server.ps.service;

import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.szholly.data.general.*;
import com.szholly.plug.form.excel.utils.StringUtil;
import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.pro.watergas_server.common.data.ListPageResult;
import com.szholly.pro.watergas_server.common.util.DateHelper;
import com.szholly.pro.watergas_server.common.util.RedisDbExecute;
import com.szholly.pro.watergas_server.common.util.RedisHelper;
import com.szholly.pro.watergas_server.common.util.RowBaseCompare;
import com.szholly.pro.watergas_server.common.web.RequestUnit;
import com.szholly.pro.watergas_server.ps.monitor.MonitorDataType;
import com.szholly.pro.watergas_server.ps.monitor.MonitorWaterType;
import com.szholly.utils.spring.SpringBeanFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import net.sf.json.JSONObject;

/**
 * 数据报表服务
 * 
 * @author WINSON
 * @since 2015-12-31
 *
 */
@Service
public class DataReportService {

	@Autowired
	DomainService domainService;
	
	@Autowired
	OnlineMonitorService onlineMonitorService;
	MonitorWaterType monitorWaterType=new MonitorWaterType();
	/**
	 * 地表水测量值小数位保留规则
	 */
	private HashMap<String, Integer> pollNumSW ;
	
	/**
	 * 污水测量值小数位保留规则
	 */
	private HashMap<String, Integer> pollNumWS ;
	
	/**
	 * 海口永庄测量值小数位保留规则
	 */
	private HashMap<String, Integer> pollNumHaiNan ;
	
	/**
	 * 供水测量值小数位保留规则
	 */
	private HashMap<String, Integer> pollNumSupplyWater ;
	
	public DataReportService(){
		pollNumSW = new HashMap<>();
		pollNumSW.put("011", 1);//COD
		pollNumSW.put("060", 2);//氨氮
		pollNumSW.put("001", 2);//pH
		pollNumSW.put("COND", 1);//电导率
		pollNumSW.put("DO", 1);//溶解氧
		pollNumSW.put("015", 3);//TOC
		pollNumSW.put("065", 2);//总氮
		pollNumSW.put("029", 2);//总铜
		pollNumSW.put("023", 3);//总铬
		pollNumSW.put("028", 2);//总镍
		pollNumSW.put("031", 2);//总锰
		pollNumSW.put("020", 2);//总汞
		pollNumSW.put("PHTEM", 1);//水温
		pollNumSW.put("101", 3);//总磷
		
		pollNumWS = new HashMap<>();
		pollNumWS.put("011", 2);//COD
		pollNumWS.put("060", 3);//氨氮
		pollNumWS.put("001", 2);//pH
		pollNumWS.put("101", 3);//总磷
		
		pollNumSupplyWater = new HashMap<>();
		pollNumSupplyWater.put("001", 2);
		pollNumSupplyWater.put("total_ci", 2);
		pollNumSupplyWater.put("p", 2);
		pollNumSupplyWater.put("ss", 2);
		
		pollNumHaiNan=new HashMap<>();
		pollNumHaiNan.put("PHTEM", 1);//水温
		pollNumHaiNan.put("001", 2);//PH
		pollNumHaiNan.put("COND", 0);//电导率
		pollNumHaiNan.put("SS", 1);//浊度
		pollNumHaiNan.put("060", 2);//氨氮
		pollNumHaiNan.put("011", 1);//高猛酸盐指数
		pollNumHaiNan.put("065", 2);//总氮
		pollNumHaiNan.put("101", 3);//总磷
		pollNumHaiNan.put("LEVEL_R", 3);//水位
		pollNumHaiNan.put("NO3", 3);//硝氮
		pollNumHaiNan.put("PHE", 5);//挥发酚
		pollNumHaiNan.put("CN", 2);//氟化物
		pollNumHaiNan.put("ROOM_TEM", 1);//温度
		pollNumHaiNan.put("ROOM_H", 1);//湿度
		pollNumHaiNan.put("CHLOROPHYLL_A",1);//叶绿素a
		pollNumHaiNan.put("ORP",0);//ORP
		pollNumHaiNan.put("BLUEGREEN_ALGAE",0);//蓝绿藻
	}
	
	/**
	 * 获取实时数据
	 * @param params
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public ListPageResult getRealTimeData(Map<String,String> params,PageInfo pageInfo) throws Exception {

		String monitorEquipmentNo = params.get("mn");
		String waterType = params.get("waterType");
		if(monitorEquipmentNo == null ||monitorEquipmentNo.equals("")){
			return null;
		}
		String parmItems = "";
		String timeWhere = "";
		
		String parmToChecks = params.get("parmToChecks");
		String[] parms = parmToChecks.split(",");
		
		
		for(String parmName : parms){
			parmItems += ",'"+parmName+"' P"+parmName+"AVG";
		}
		parmItems = parmItems.replaceFirst(",", "");
		
		if(params.containsKey("start") && params.containsKey("end")){
			timeWhere = " datatime>=to_date('" + params.get("start") + "','YYYY-MM-DD HH24:MI:SS') and  datatime<to_date('" + params.get("end") + "','YYYY-MM-DD HH24:MI:SS') ";
		}
		else{
			timeWhere = "datatime > sysdate - 1/24 * 0.5";
		}

		String sql = "SELECT * FROM ( select * from ( select datatime,PARMNAME,rtd,mn from monitor_data where 1=1 and "+timeWhere+" AND mn = @mn and flag = 'N' ORDER BY DATATIME) t \n"
				+ " pivot(avg(rtd) for PARMNAME in ("+parmItems+"))\n"
						+ " ) t order by datatime desc ";
		
		List<GlobalParameter> pc = new ArrayList<>();
		pc.add(new GlobalParameter("mn", monitorEquipmentNo));
		
		ListPageResult result = DataUtil.getPageListResult(sql, pc,null);
		List<RowBase> list = result.getList();
		list = NumPointFormat(list,parms,waterType);//格式化小数点
		list = GenerateAlarmInfo(list, parms, monitorEquipmentNo);//生成报警信息
		
		result.setList(list);
		
		return result;
	}
	
	/**
	 * 获取所有站点的当前实时数据
	 * gislin
	 * 2016年5月7日
	 * @param params
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public HashMap<String, Object> AllStationRealTimeData(String waterType,String parmToChecks) throws Exception {

		SimpleDateFormat sp = new SimpleDateFormat("yyyyMMddHHmmss");
		SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String parmItems = "";
		String[] parms = parmToChecks.split(",");
		JSONObject[] headText = new JSONObject[parms.length];
		
		for(int i=0;i<parms.length;i++){
			String parmName = parms[i];
			parmItems += ",'"+parmName+"' P"+parmName;
			String pollName = RedisHelper.getInstance().translationDictValue("MONITOR_PARAM", parmName);
			String unitText = RedisHelper.getInstance().translationDictUnit("MONITOR_PARAM", parmName);
			if(unitText != null && !unitText.equals("")){
				pollName = pollName + "("+unitText+")";
			}
			JSONObject obj = new JSONObject();
			obj.put("pollCode",parmName.toUpperCase());
			obj.put("pollName",pollName);
			headText[i] = obj;
		}
		parmItems = parmItems.replaceFirst(",", "");
		
		Pattern p = Pattern.compile("stationInfo:(.*):deviceInfo:DataTime");
		
		String redisKeyPrix = "stationInfo:*:deviceInfo:DataTime";
		Set<String> keys = RedisDbExecute.queryKeys(redisKeyPrix);
		
		List<RowBase> list = new ArrayList<>();
		HashMap<String, Object> result = new HashMap<>();
		
		Matcher m = null;
		for(String key : keys){
			m = p.matcher(key);
			
			if(m.find()){
				RowBase row = new RowBase();
				String mn = m.group(1);
				
				String stationType = RedisHelper.getInstance().getStationTypeByMn(mn);
				
				if(stationType == null || !stationType.equals(waterType)){
					continue;
				}
				
				String stationName = RedisHelper.getInstance().getStationNameByMN(mn);
				String companyName = RedisHelper.getInstance().getStationCompanyNameByMN(mn);
				
				row.setValue("stationName", stationName);
				row.setValue("companyName", companyName);
				for(String pollCode : parms){
					String value = RedisDbExecute.getString("stationInfo:"+mn+":deviceInfo:"+pollCode+"_MEAS");
					if(value == null){
						value = RedisDbExecute.getString("stationInfo:"+mn+":deviceInfo:"+pollCode);
					}
					row.setValue(pollCode, value);
				}
				String datatimeStr = RedisDbExecute.getString(key);
				if(datatimeStr.contains("\"")){
					datatimeStr=datatimeStr.replace("\"", "");
				}
				try {
					String datatimeStrf = sf.format(sp.parse(datatimeStr));
					row.setValue("DATATIME", datatimeStrf);
				} catch (Exception e) {

				}
				list.add(row);
			}
		}
		
		list = NumPointFormat(list, parms,waterType);
		
		RowBaseCompare rowBaseComparer = new RowBaseCompare();
		rowBaseComparer.addComparatorItem(rowBaseComparer.new ComparatorItem("companyName"));
		rowBaseComparer.addComparatorItem(rowBaseComparer.new ComparatorItem("stationName"));
		Collections.sort(list, rowBaseComparer);
		
		result.put("dataList", list);
		result.put("headText", headText);
		
		return result;
	}
	/**
	 * 数据一致性
	 * @param parms
	 * @return
	 */
	public List<Object> queryCoherencedata(Map<String,String> parms){
		//查询水司SQL语句
		String waterSql="select t3.name,t2.wwtp_id,count(t1.stationid)as cohCount from coherencedata t1"      
       +" left join BC_SITE_INFO t2 on t2.code=t1.stationid  left join BC_ENTERPRISE_ATTR t3 on t3.code=t2.wwtp_id"
       +" left join safe_user t4 on t4.s_userid=t1.userid where 1=1 and t2.station_category='3' ";		
		//查询站点SQL语句
		String stationSql="select t3.name,t2.wwtp_id,t2.name,t2.code,count(t1.stationid) as stationCount from coherencedata t1  left join BC_SITE_INFO t2 on t2.code=t1.stationid"
				+" left join BC_ENTERPRISE_ATTR t3 on t3.code=t2.wwtp_id  left join safe_user t4 on t4.s_userid=t1.userid"
				
				+" where 1=1 and t2.station_category='3' ";		
		//数据一致性SQL语句
		String CoherenceSql="select  t4.c_userealname, t2.name,t5.s_domaincaption, cast(t1.rtd as decimal(18,2)) as rtd,cast(t1.value as decimal(18,2)) as value,t1.stationid,t1.thandate from coherencedata t1"
				+" left join data_defulatdomain t5 on t5.s_domaincode=t1.parmname and t5.s_domainname='深圳供水参数'"
				
				+" left join BC_SITE_INFO t2 on t2.code=t1.stationid  left join safe_user t4 on t4.s_userid=t1.userid"
				+"  left join BC_ENTERPRISE_ATTR t3 on t3.code = t2.wwtp_id  where 1=1 and t2.station_category='3'";
		//监测人员
		String userSql="select t2.code,t2.name,t3.c_userealname,t3.s_userid,count(t1.parmname) as comCount from coherencedata t1" 
				+" left join BC_SITE_INFO t2 on t2.code=t1.stationid left join safe_user t3 on t3.s_userid=t1.userid"
				+" left join BC_ENTERPRISE_ATTR t3 on t3.code = t2.wwtp_id "
				+" where 1=1 and t2.station_category='3' ";	
		if(parms.containsKey("station_id")) {
			waterSql += " and t1.stationid='"+parms.get("station_id")+"'";
			stationSql+="  and t1.stationid='"+parms.get("station_id")+"'";
			CoherenceSql+="  and t1.stationid='"+parms.get("station_id")+"'";
			userSql+="  and t1.stationid='"+parms.get("station_id")+"'";
   		}
		if(parms.containsKey("username")){
			waterSql+=" and t4.c_userealname like '%"+parms.get("username")+"%'";
			stationSql+=" and t4.c_userealname like '%"+parms.get("username")+"%'";
			CoherenceSql+=" and t4.c_userealname like '%"+parms.get("username")+"%'";
			userSql+=" and t3.c_userealname like '%"+parms.get("username")+"%'";
		}
		if(parms.containsKey("start")){
			waterSql+=" and t1.thandate>=to_date('"+ parms.get("start")+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
			stationSql+=" and t1.thandate>=to_date('"+ parms.get("start")+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
			CoherenceSql+=" and t1.thandate>=to_date('"+ parms.get("start")+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
			userSql+=" and t1.thandate>=to_date('"+ parms.get("start")+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
		}
		if(parms.containsKey("end")){
			waterSql+=" and t1.thandate<=to_date('"+ parms.get("end")+ " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
			stationSql+=" and t1.thandate<=to_date('"+ parms.get("end")+ " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
			CoherenceSql+=" and t1.thandate<=to_date('"+ parms.get("end")+ " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
			userSql+=" and t1.thandate<=to_date('"+ parms.get("end")+ " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
		}
		waterSql+="  group by t3.name,t2.wwtp_id order by t3.name";
		stationSql+=" group by t3.name,t2.wwtp_id,t2.name,t2.code order by t3.name,t2.name";
		CoherenceSql+=" group by t2.wwtp_id,t2.name,t1.thandate, t4.c_userealname,t5.s_domaincaption,t1.rtd,t1.value,t1.stationid,t3.name order by  t3.name,t2.name,t4.c_userealname,t1.thandate asc";
		userSql+="  group by t2.name, t3.c_userealname, t2.code, t3.s_userid,t3.name order by t3.name,t2.name,t3.c_userealname asc";
		List<GlobalParameter> listParam = new ArrayList<>();
		List<RowBase> waterList=DataUtil.getSelectRows(waterSql, listParam);//获取水司
		List<RowBase> stationList=DataUtil.getSelectRows(stationSql, listParam);//获取站点
		List<RowBase> CoherenceList=DataUtil.getSelectRows(CoherenceSql, listParam);//获取数据一致性数据
		List<RowBase> userList=DataUtil.getSelectRows(userSql, listParam);//获取监测人
		List<Object> dataList=new ArrayList<>();
		HashMap<Object,Object> data=new HashMap<>();
		data.put("waterList", waterList);
		data.put("stationList", stationList);
		data.put("CoherenceList", CoherenceList);
		data.put("userList", userList);
		dataList.add(data);
		return dataList;
	}
	public List<Object> exportCoherencedata(Map<String,String> parms){
		String sql="select t3.name as waterNmae,t2.wwtp_id,t2.name,t2.code,t5.s_domaincaption, cast(t1.rtd as decimal(18, 2)) as rtd,"
		 +" cast(t1.value as decimal(18, 2)) as value,t1.stationid, t1.thandate, t4.c_userealname,t4.s_userid"
		 +" from coherencedata t1 left join data_defulatdomain t5 on t5.s_domaincode = t1.parmname"
		 +" and t5.s_domainname = '深圳供水参数'left join BC_SITE_INFO t2 on t2.code = t1.stationid"
		 +" left join safe_user t4 on t4.s_userid = t1.userid left join BC_ENTERPRISE_ATTR t3 on t3.code = t2.wwtp_id"
		 +" where 1 = 1 and t2.station_category = '3'";
		if(parms.containsKey("station_id")) {
			sql+="  and t1.stationid='"+parms.get("station_id")+"'";
   		}
		if(parms.containsKey("username")){
			sql+=" and t4.c_userealname like '%"+parms.get("username")+"%'";
		}
		if(parms.containsKey("start")){
			sql+=" and t1.thandate>=to_date('"+ parms.get("start")+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
		}
		if(parms.containsKey("end")){
			sql+=" and t1.thandate<=to_date('"+ parms.get("end")+ " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
		}
		sql+="group by t2.wwtp_id,t2.name,t1.thandate,t5.s_domaincaption, t4.c_userealname, t1.rtd,t1.value,t2.code,t4.s_userid,"
			+" t1.stationid, t3.name  order by t3.name, t2.name, t4.c_userealname,t1.thandate asc";
		List<GlobalParameter> listParam = new ArrayList<>();
		List<RowBase> CoherenceList=DataUtil.getSelectRows(sql, listParam);//获取数据一致性数据
		String watername=null;
		String stationName=null;
		String username=null;
		List<Object> CoherenceData=new ArrayList<>();
		List<Object> textList=queryCoherencedata(parms);
		for(RowBase row:CoherenceList){
			HashMap<Object,Object> rowMap=new HashMap<>();
			@SuppressWarnings("unchecked")
			HashMap<Object,Object> haswater=(HashMap<Object, Object>) textList.get(0);
			@SuppressWarnings("unchecked")
			List<RowBase> waterlist=(List<RowBase>) haswater.get("waterList");
			@SuppressWarnings("unchecked")
			List<RowBase> userList=(List<RowBase>) haswater.get("userList");
			@SuppressWarnings("unchecked")
			List<RowBase> stationlist=(List<RowBase>) haswater.get("stationList");
			//水司
			if(watername==null){
				watername=row.getSafeString("WATERNMAE");			
				
				//统计水司数据一致性记录数
				for(RowBase water:waterlist){
					String waterid=water.getSafeString("WWTP_ID");
					if(row.getSafeString("WWTP_ID").equals(waterid)){
						rowMap.put("waterCount",water.getSafeString("COHCOUNT"));
					}
				}
				rowMap.put("watername",watername);
			}else{
				if(!row.getSafeString("WATERNMAE").equals(watername)){
					//统计水司数据一致性记录数
					for(RowBase water:waterlist){
						String waterid=water.getSafeString("WWTP_ID");
						if(row.getSafeString("WWTP_ID").equals(waterid)){
							rowMap.put("waterCount",water.getSafeString("COHCOUNT"));
						}
					}
					watername=row.getSafeString("WATERNMAE");
					rowMap.put("watername",watername);
				}else{
					rowMap.put("watername", "");
				}
			}
			//站点
			if(stationName==null){
				stationName=row.getSafeString("NAME");				
				username=row.getSafeString("C_USEREALNAME");		
				//统计站点数据一致性记录数
				for(RowBase stationrow:stationlist){
					String stationcode=stationrow.getSafeString("CODE");
					if(row.getSafeString("CODE").equals(stationcode)){
						rowMap.put("stationcount", stationrow.getSafeString("STATIONCOUNT"));
					}
				}
				//统计用户数据一致性记录数
				for(RowBase userrow:userList){
					String userid=userrow.getSafeString("S_USERID");
					String stationcode=userrow.getSafeString("CODE");
					if(row.getSafeString("S_USERID").equals(userid)&&row.getSafeString("CODE").equals(stationcode)){
						rowMap.put("USERCOUNT", userrow.getSafeString("COMCOUNT"));						
					}
				}
				rowMap.put("username", username);
				rowMap.put("stationName", stationName);
			}else{
				if(!row.getSafeString("NAME").equals(stationName)){
					//统计站点数据一致性记录数
					for(RowBase stationrow:stationlist){
						String stationcode=stationrow.getSafeString("CODE");
						
						if(row.getSafeString("CODE").equals(stationcode)){
							rowMap.put("stationcount", stationrow.getSafeString("STATIONCOUNT"));
						}
					}
					//统计用户数据一致性记录数
					for(RowBase userrow:userList){
						String userid=userrow.getSafeString("S_USERID");
						String stationcode=userrow.getSafeString("CODE");
						if(row.getSafeString("S_USERID").equals(userid)&&row.getSafeString("CODE").equals(stationcode)){
							rowMap.put("USERCOUNT", userrow.getSafeString("COMCOUNT"));							
						}
					}
					stationName=row.getSafeString("NAME");
					username=row.getSafeString("C_USEREALNAME");
					rowMap.put("username", username);
					rowMap.put("stationName", stationName);
				}else{
					rowMap.put("stationName", "");
					if(!row.getSafeString("C_USEREALNAME").equals(username)){
						//统计用户数据一致性记录数						
						for(RowBase userrow:userList){
							String userid=userrow.getSafeString("S_USERID");
							String stationcode=userrow.getSafeString("CODE");
							if(row.getSafeString("S_USERID").equals(userid)&&row.getSafeString("CODE").equals(stationcode)){
								rowMap.put("USERCOUNT", userrow.getSafeString("COMCOUNT"));								
							}
						}
						username=row.getSafeString("C_USEREALNAME");
						rowMap.put("username", username);
					}else{
						rowMap.put("username", "");
					}
				}
			}
			rowMap.put("Coherence", row);
			CoherenceData.add(rowMap);
		}
		
		return CoherenceData;
	}
	public String StationFilter(){
		
		return null;
	}
	/**
	 * 获取数据比对站点
	 * @return
	 */
	public List<RowBase> queryCoherencedataStation(Map<String,String> parms){
		String sql="select t2.c_userealname,t3.name,t3.code,t2.s_userid from coherencedata t1 left join safe_user t2 on t2.s_userid=t1.userid "
				+" left join BC_SITE_INFO t3 on t3.code=t1.stationid where 1=1";
		if(parms.containsKey("station_id")) {
   			sql += " and t1.stationid='"+parms.get("station_id")+"'";
   		}
		if(parms.containsKey("start")){
			sql+=" and t1.thandate>=to_date('"+ parms.get("start")+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
		}
		if(parms.containsKey("end")){
			sql+=" and t1.thandate<=to_date('"+ parms.get("end")+ " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
		}
		sql+=" group by t3.name, t2.c_userealname,t3.code,t2.s_userid";
		List<GlobalParameter> listParam = new ArrayList<>();
		return DataUtil.getSelectRows(sql, listParam);
	}
	/**
	 * 获取数据比对项
	 * @return
	 */
	public List<RowBase> queryCoherencedataList(Map<String,String> parms){
		String sql="select t4.s_domaincaption,cast(t1.rtd as decimal(18,2))as rtd,t1.value,t1.thandate,t1.stationid,t2.s_userid from coherencedata t1 left join safe_user t2 on t2.s_userid=t1.userid "
				+" left join BC_SITE_INFO t3 on t3.code=t1.stationid "
				+" left join data_defulatdomain t4 on t4.s_domaincode=t1.parmname and t4.s_domainname='深圳供水参数' where 1=1";
		if(parms.containsKey("station_id")) {
		sql += " and t1.stationid='"+parms.get("station_id")+"'";
   		}
		if(parms.containsKey("start")){
			sql+=" and t1.thandate>=to_date('"+ parms.get("start")+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
		}
		if(parms.containsKey("end")){
			sql+=" and t1.thandate<=to_date('"+ parms.get("end")+ " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
		}
		sql+=" order by t1.thandate,t4.s_domaincaption asc";
		List<GlobalParameter> listParam = new ArrayList<>();
		return DataUtil.getSelectRows(sql, listParam);
	}
	/**
	 * 获取站点报警阈值
	 * @param wwtp_id
	 * @param flow_type
	 * @param station_id
	 * @return
	 */
	public JSONObject getAlarmInfo(String mn,String[] parms) {

		HashMap<String, Double> alarmInfo = new HashMap<>();
		
		for(String poll : parms){
			try {
				double down = RedisHelper.getInstance().getPollAlarmDown(mn, poll);
				alarmInfo.put("DOWNP" + poll.toUpperCase() + "AVG", down);
				
			} catch (Exception e) {
				
			}
			
			try {
				double up = RedisHelper.getInstance().getPollAlarmUp(mn, poll);
				alarmInfo.put("UPP" + poll.toUpperCase() + "AVG", up);
			} catch (Exception e) {

			}
			
			try {
				double up = RedisHelper.getInstance().getPollAbnormalUp(mn, poll);
				alarmInfo.put("UPODDP" + poll.toUpperCase() + "AVG", up);
			} catch (Exception e) {

			}
			
			try {
				double Down = RedisHelper.getInstance().getPollAbnormalDown(mn, poll);
				alarmInfo.put("DOWNODDP" + poll.toUpperCase() + "AVG", Down);
			} catch (Exception e) {

			}
		}

		JSONObject alarmInfoJson = JSONObject.fromObject(alarmInfo);
		return alarmInfoJson;
	};
	/**
	 * 获取站点报警通量阈值
	 * @param wwtp_id
	 * @param flow_type
	 * @param station_id
	 * @return
	 */
	public JSONObject getAlarmInfoCou(String station_id) {

		String sql = "select DENSITY_ALARM_DOWMLIMIT down,DENSITY_ALARM_UPLIMIT up,sp.name from BC_SITE_POLL sp\n"
				+ "       left join BC_SITE_INFO ms on ms.exp_no = sp.site_id\n"
				+ "       where 1=1";
		
		List<GlobalParameter> listParam = new ArrayList<>();
		
		if(StringUtil.IsNotNullOrEmpty(station_id)){
			sql += " and sp.site_id=@station_id";
			listParam.add(new GlobalParameter("station_id", station_id));
		}

		List<RowBase> list = DataUtil.getSelectRows(sql, listParam);
		
		HashMap<String, Double> alarmInfo = new HashMap<>();

		for (int i = 0; i < list.size(); i++) {
			RowBase row = list.get(i);
			String name = row.get("NAME") + "";

			try {
				double down = Double.parseDouble(row.get("DOWN") + "");
				alarmInfo.put("DOWNP" + name + "COU", down);
				
			} catch (Exception e) {
				
			}
			
			try {
				double up = Double.parseDouble(row.get("UP") + "");
				alarmInfo.put("UPP" + name + "COU", up);
			} catch (Exception e) {

			}
		}
		JSONObject alarmInfoJson = JSONObject.fromObject(alarmInfo);
		return alarmInfoJson;
	};
	
	/**
	 * 给报表添加报警信息
	 * gislin
	 * 2016年8月5日
	 * @param list 列表数据
	 * @param parms 要分析的污染物集合
	 * @param mn 设备编号
	 * @return
	 */
	public List<RowBase> GenerateAlarmInfo(List<RowBase> list,String[] parms,String mn){
		
		HashMap<String, Double> pollAlarmMap = new HashMap<>();
		
		for(RowBase row : list){
			
			for(String poll:parms){
				String fieldName  = "P"+poll.toUpperCase();
				Double value = row.getDouble(fieldName);
				
				row.setValue("P"+poll.toLowerCase()+"_ALARM_FLAG", 0);//0表示正常
				
				if(value == null){
					fieldName  = "P"+poll.toUpperCase()+"AVG";
					value = row.getDouble(fieldName);
				}
				
				if(value == null){
					fieldName  = poll.toUpperCase();
					value = row.getDouble(fieldName);
				}
				
				if(value == null){
					continue;
				}
				
				Double overUp = null;
				Double overDown = null;
				
				if(pollAlarmMap.containsKey(poll+"_alarmUp")){
					overUp = pollAlarmMap.get(poll+"_alarmUp");
				}
				else{
					overUp = RedisHelper.getInstance().getPollAlarmUp(mn, poll);
					pollAlarmMap.put(poll+"_alarmUp",overUp);
				}
				
				if(pollAlarmMap.containsKey(poll+"_alarmDown")){
					overDown = pollAlarmMap.get(poll+"_alarmDown");
				}
				else{
					overDown = RedisHelper.getInstance().getPollAlarmDown(mn, poll);
					pollAlarmMap.put(poll+"_alarmDown",overDown);
				}
				
				if(overUp != null && value > overUp){
					row.setValue("P"+poll.toLowerCase()+"_ALARM_FLAG", "ALARM_OVER");//表示超标
				}
				
				if(overDown != null && value < overDown){
					row.setValue("P"+poll.toLowerCase()+"_ALARM_FLAG", "ALARM_LESS");//表示小于低限
				}
				
				Double abnormalUp = null;
				Double abnormalDown = null;
				
				if(pollAlarmMap.containsKey(poll+"_abnormalUp")){
					abnormalUp = pollAlarmMap.get(poll+"_abnormalUp");
				}
				else{
					abnormalUp = RedisHelper.getInstance().getPollAbnormalUp(mn, poll);
					pollAlarmMap.put(poll+"_abnormalUp",abnormalUp);
				}
				
				if(pollAlarmMap.containsKey(poll+"_abnormalDown")){
					abnormalDown = pollAlarmMap.get(poll+"_abnormalDown");
				}
				else{
					abnormalDown = RedisHelper.getInstance().getPollAbnormalDown(mn, poll);
					pollAlarmMap.put(poll+"_abnormalDown",abnormalDown);
				}
				
				if(abnormalUp != null && value > abnormalUp){
					row.setValue("P"+poll.toLowerCase()+"_ALARM_FLAG", "ODD_OVER");//表示超高异常
				}
				
				if(abnormalDown != null && value < abnormalDown){
					row.setValue("P"+poll.toLowerCase()+"_ALARM_FLAG", "ODD_LESS");//表示超低异常
				}
			}
		}
		return list;
	}
	
	/**
	 * 数据小数点格式化
	 * gislin
	 * 2016年5月26日
	 * @param list
	 * @return
	 */
	public List<RowBase> NumPointFormat(List<RowBase> list,String[] parms,String waterType) {
		
		for(RowBase row : list){
			for(String poll : parms){
				String fieldName  = "P"+poll.toUpperCase();
				Object value = row.get(fieldName);
				if(value == null){
					fieldName  = "P"+poll.toUpperCase()+"AVG";
					value = row.get(fieldName);
				}
				
				if(value == null){
					fieldName  = poll.toUpperCase();
					value = row.get(fieldName);
				}
				
				String finalValue = NumPointFormat(poll, value,waterType);
				if(finalValue != null){
					row.setValue(fieldName, finalValue);
				}
				
				//转换排放量数据
				fieldName  = "P"+poll.toUpperCase()+"COU";
				if(row.containsKey(fieldName)){
					value = row.get(fieldName);
					finalValue = NumPointFormat(poll, value,waterType);
					row.setValue(fieldName, finalValue);
				}
			}
		}
		return list;
	}
	
	/**
	 * 测量数值格式化，单个参数
	 * gislin
	 * 2016年5月26日
	 * @param poll
	 * @param value
	 * @param waterType 系统类型
	 * @return
	 */
	public String NumPointFormat(String poll,Object value,String waterType){
		
		if(value == null){
			return null;
		}
		
		BigDecimal doubleValue = null;
		
		try {
			doubleValue  =  new BigDecimal(value.toString());
		} catch (Exception e) {
			return value.toString();
		}
		
		HashMap<String, Integer> pollNum = new HashMap<>();
		
		if("1".equals(waterType)){
			pollNum = pollNumSW;
		}
		else if("0".equals(waterType)){
			pollNum = pollNumWS;
		}
		else if("3".equals(waterType)){
			pollNum = pollNumSupplyWater;
		}
		else if("7".equals(waterType)){
			pollNum = pollNumHaiNan;
		}

		Integer pointNum = pollNum.get(poll.toUpperCase());
		String finalValue = null;
		
		if(pointNum == null){
			finalValue = setDataScale(doubleValue.doubleValue(),2);
			return finalValue;
		}
		if(pointNum == 0){
			finalValue = setDataScale(doubleValue.doubleValue(),0);
		}
		else if(pointNum == 1){
			finalValue = setDataScale(doubleValue.doubleValue(),1);
		}
		else if(pointNum == 2){
			finalValue = setDataScale(doubleValue.doubleValue(),2);
		}
		else if(pointNum == 3){
			finalValue = setDataScale(doubleValue.doubleValue(),3);
		}else if(pointNum == 4){
			finalValue = setDataScale(doubleValue.doubleValue(),3);
		}
		else if(pointNum == 5){
			finalValue = setDataScale(doubleValue.doubleValue(),3);
		}
		return finalValue;
	}
	
	/**
	 * 获取浓度历史数据
	 * @param params 
	 *  wwtp_id = params.get("wwtp_id") <br>
	 *  flow_type = params.get("flow_type") <br>
		station_id = params.get("station_id") <br>
		start = params.get("start") <br>
		end = params.get("end") <br>
		dataSource = params.get("dataSource") <br>
		parmToChecks = params.get("parmToChecks") <br>
	 * @param pageInfo 分页信息，为空的话，不分页
	 * @param isForMondifyCation 是否修约模式的查询
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getHistoricalData(Map<String,String> params,PageInfo pageInfo) throws Exception {
		
		String wwtp_id = params.get("wwtp_id");
		String start = params.get("start");
		String end = params.get("end");
		String dataSource = params.get("dataSource");
		String parmToChecks = params.get("parmToChecks");
		String mn = params.get("mn");
		String isForModify = params.get("isForModify");
		String waterType = RequestUnit.getParameter("WATER_TYPE");
		boolean isForMondifyCation = false;
		
		if(isForModify != null && isForModify.toUpperCase().equals("TRUE")){
			isForMondifyCation = true;
		}
		
		String[] parms = parmToChecks.split(",");
		
		String getDataSql = "";
		if("5".equals(dataSource)){
			getDataSql = getAmountSql(wwtp_id,parms,start,end);
		}
		else{
			getDataSql = getHistoricalDataSql(mn, parms, start, end, dataSource,isForMondifyCation);
		}
		List<RowBase> list = null;
		
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable table = tableFactory.openTable("MONITOR_DATA");
		
		if (pageInfo==null) {
			list = DataUtil.getSelectRows(getDataSql, null, table);
		} else {
			list = DataUtil.getPageRow(getDataSql, null, table,pageInfo);
		}
		
		list = NumPointFormat(list, parms,waterType);
		list = GenerateAlarmInfo(list, parms, mn);
		
		return list;
	}
	/**
	 * 获取通量历史数据
	 * @param params 
	 *  wwtp_id = params.get("wwtp_id") <br>
	 *  flow_type = params.get("flow_type") <br>
		station_id = params.get("station_id") <br>
		start = params.get("start") <br>
		end = params.get("end") <br>
		dataSource = params.get("dataSource") <br>
		parmToChecks = params.get("parmToChecks") <br>
	 * @param pageInfo 分页信息，为空的话，不分页
	 * @param isForMondifyCation 是否修约模式的查询
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> getHistoricalDataCou(Map<String,String> params,PageInfo pageInfo) throws Exception {
		
		String wwtp_id = params.get("wwtp_id");
		String start = params.get("start");
		String end = params.get("end");
		String dataSource = params.get("dataSource");
		String parmToChecks = params.get("parmToChecks");
		String mn = params.get("mn");
		String isForModify = params.get("isForModify");
		String waterType = RequestUnit.getString("waterType");
		boolean isForMondifyCation = false;
		
		if(isForModify != null && isForModify.toUpperCase().equals("TRUE")){
			isForMondifyCation = true;
		}
		
		String[] parms = parmToChecks.split(",");
		
		String getDataSql = "";
		if("5".equals(dataSource)){
			getDataSql = getAmountSql(wwtp_id,parms,start,end);
		}
		else{
			getDataSql = getHistoricalDataCouSql(mn, parms, start, end, dataSource,isForMondifyCation);
		}
		List<RowBase> list = null;
		
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable table = tableFactory.openTable("MONITOR_DATA");
		
		if (pageInfo==null) {
			list = DataUtil.getSelectRows(getDataSql, null, table);
		} else {
			list = DataUtil.getPageRow(getDataSql, null, table,pageInfo);
		}
		
		list = NumPointFormat(list, parms,waterType);
		
		return list;
	}
	/**
	 * 获取浓度历史数据查询SQL
	 * 
	 * @param monitorEquipmentNo
	 *            设备统一编号
	 * @param parms
	 *            查询参数：
	 * @param start
	 *            开始时间
	 * @param end
	 *            结束时间
	 * @param dataSource
	 *            数据源
	 * @param isForMondifyCation 是否修约模式的查询
	 * @param dataType 数据类型，avg浓度均值，cou排放量
	 * @return
	 * @throws ParseException 
	 */
	private String getHistoricalDataSql(String monitorEquipmentNo, String[] parms, String start, String end,
			String dataSource,boolean isForMondifyCation) throws ParseException {

		int iDataSource = Integer.parseInt(dataSource);
		String waterType = RequestUnit.getParameter("WATER_TYPE");
		String tableName = "monitor_data";
		if(StringUtil.IsNotNullOrEmpty(waterType)&&!waterType.equals("3")){
			String tableType="second";
			if(dataSource.equals("2")){
				tableType="minute";
			}
			if(dataSource.equals("3")){
				tableType="hour";
			}
			if(dataSource.equals("4")){
				tableType="day";
			}
			if(dataSource.equals("6")){
				tableType="cycle";
			}
			MonitorDataType md=monitorWaterType.getWaterType(waterType, tableType);
			tableName=md.getTableName();
		}
		else{
			switch (iDataSource) {
			case 2:
				tableName = "monitor_data_min";
				break;
			case 3:
				tableName = "monitor_data_hour";
				break;
			case 4:
				tableName = "monitor_data_day";
				break;
			case 6:
				tableName = "monitor_data_cycle";
				break;
			case 7:
				tableName = "MONITOR_DATA_DAY_CONFIGSOFT ";
				break;
			case 8:
				tableName = "MONITOR_DATA_HOUR_CONFIGSOFT";
				break;
			}
			
		}
		
		String parmAvgItems = "";
		String parmFlagItems = "";
		
		for(String parmName : parms){
			parmAvgItems += ",'"+parmName+"' P"+parmName+"AVG";
			parmFlagItems += ",'"+parmName+"' P"+parmName+"FLAG";
		}
		parmAvgItems = parmAvgItems.replaceFirst(",", "");
		parmFlagItems = parmFlagItems.replaceFirst(",", "");
		String sql ="";
		//wangl 2016-12-26 历史数据日均值 显示时间改完xxxx-xx-xx
		if(iDataSource == 4){
			sql = "SELECT * FROM (\n" +
					"       select * from ( select to_char(datatime,'hh24') hour,to_char(datatime,'dd') day,to_char(datatime,'yyyy-mm-dd') datatime,PARMNAME,avg,QN,DATA_FROM,MODIFY_CERTIFICATE_ID from "+tableName+" t1 \n"
					+ "			where datatime >= to_date('" + start + "','YYYY-MM-DD HH24:MI:SS') AND datatime <= to_date('" + end + "','YYYY-MM-DD HH24:MI:SS') AND MN = '"+monitorEquipmentNo+"' and  PARMNAME!='COMP_POLL_INDEX' AND t1.FLAG = 'N'  ) t\n" + 
					"              pivot(avg(avg) for PARMNAME in ("+parmAvgItems+"))\n" + 
					"       ) TAVG\n"
					;	
		}
		else{
			sql = "SELECT * FROM (\n" +
					"       select * from ( select to_char(datatime,'hh24') hour,to_char(datatime,'dd') day,datatime,PARMNAME,avg,QN,DATA_FROM,MODIFY_CERTIFICATE_ID from "+tableName+" t1 \n"
					+ "			where datatime >= to_date('" + start + "','YYYY-MM-DD HH24:MI:SS') AND datatime <= to_date('" + end + "','YYYY-MM-DD HH24:MI:SS') AND MN = '"+monitorEquipmentNo+"' and  PARMNAME!='COMP_POLL_INDEX' AND t1.FLAG = 'N'  ) t\n" + 
					"              pivot(avg(avg) for PARMNAME in ("+parmAvgItems+"))\n" + 
					"       ) TAVG\n"
					;	
		}
		 
		
		//如果是周期数据,把数据标识显示出来
		if(iDataSource == 6 || isForMondifyCation){
			//周期数据要全部类型的数据
			sql = sql.replace("AND t1.FLAG = 'N'", "");
			sql +=  " left join "+
					"       (select * from ( select QN flag_qn,flag,PARMNAME from "+tableName+" t1 \n"
					+ "			where datatime >= to_date('" + start + "','YYYY-MM-DD HH24:MI:SS') AND datatime <= to_date('" + end + "','YYYY-MM-DD HH24:MI:SS') AND MN = '"+monitorEquipmentNo+"' and  PARMNAME!='COMP_POLL_INDEX') t\n" + 
					"              pivot(max(flag) for PARMNAME in ("+parmFlagItems+"))\n" + 
					"       ) TFLAG ON TFLAG.flag_qn = TAVG.QN\n";
		}
		
		if(isForMondifyCation){
			sql = generyModifySQL(start, end, iDataSource, sql);
		}
		else{
			sql +=" order by TAVG.DATATIME DESC";
		}
		
		return sql;
	}
	/**
	 * 获取通量历史数据查询SQL
	 * 
	 * @param monitorEquipmentNo
	 *            设备统一编号
	 * @param parms
	 *            查询参数：
	 * @param start
	 *            开始时间
	 * @param end
	 *            结束时间
	 * @param dataSource
	 *            数据源
	 * @param isForMondifyCation 是否修约模式的查询
	 * @param dataType 数据类型，avg浓度均值，cou排放量
	 * @return
	 * @throws ParseException 
	 */
	private String getHistoricalDataCouSql(String monitorEquipmentNo, String[] parms, String start, String end,
			String dataSource,boolean isForMondifyCation) throws ParseException {

		int iDataSource = Integer.parseInt(dataSource);
		List<String> parmList = Arrays.asList(parms);

		String tableName = "monitor_data";
		switch (iDataSource) {
		case 2:
			tableName = "monitor_data_min";
			break;
		case 3:
			tableName = "monitor_data_hour";
			break;
		case 4:
			tableName = "monitor_data_day";
			break;
		case 6:
			tableName = "monitor_data_cycle";
			break;
		}
		
		String parmAvgItems = "";
		String parmFlagItems = "";
		
		if(!parmList.contains("011")){
			parmList.add("011");
		}
		
		for(String parmName : parms){
			parmAvgItems += ",'"+parmName+"' P"+parmName+"COU";
			parmFlagItems += ",'"+parmName+"' P"+parmName+"FLAG";
		}
		parmAvgItems = parmAvgItems.replaceFirst(",", "");
		parmFlagItems = parmFlagItems.replaceFirst(",", "");
		
		String sql = "SELECT * FROM (\n" +
						"       select * from ( select to_char(datatime,'hh24') hour,to_char(datatime,'dd') day,datatime,PARMNAME,COU,QN,DATA_FROM,MODIFY_CERTIFICATE_ID from "+tableName+" t1 \n"
						+ "			where datatime >= to_date('" + start + "','YYYY-MM-DD HH24:MI:SS') AND datatime <= to_date('" + end + "','YYYY-MM-DD HH24:MI:SS') AND MN = '"+monitorEquipmentNo+"' AND FLAG = 'N' ) t\n" + 
						"              pivot(avg(COU) for PARMNAME in ("+parmAvgItems+"))\n" + 
						"       ) TAVG\n where P011COU > 0 ";
		
		if(isForMondifyCation){
			sql = generyModifySQL(start, end, iDataSource, sql);
		}
		else{
			sql +=" order by TAVG.DATATIME DESC";
		}
		
		return sql;
	}

	/**
	 * gislin
	 * 2016年6月25日
	 * @param start
	 * @param end
	 * @param iDataSource
	 * @param sf
	 * @param sql
	 * @return
	 * @throws ParseException
	 */
	private String generyModifySQL(String start, String end, int iDataSource, String sql)
			throws ParseException {
		String tempTableSql = "";
		SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		
		Calendar startCal = Calendar.getInstance();
		Date endDate = sf.parse(end);
		startCal.setTime(sf.parse(start));
		
		if(iDataSource == 3 ){
			for(int i=0;i<24;i++){
				tempTableSql += " select '"+sf.format(startCal.getTime())+"'tempDate,'"+(i < 10 ?"0"+i:i)+"' timeFlag from dual \n";
				if(i < 23){
					tempTableSql += "union all \n";
				}
				startCal.add(Calendar.HOUR_OF_DAY, 1);
			}
			
			sql += " RIGHT JOIN ("+tempTableSql+") timetemp on timetemp.timeFlag = TAVG.hour order by timetemp.timeFlag";
		}
		if(iDataSource == 6){
			for(int i=0;i<24;i+=2){
				tempTableSql += " select '"+sf.format(startCal.getTime())+"'tempDate,'"+(i < 10 ?"0"+i:i)+"' timeFlag from dual \n";
				if(i < 22){
					tempTableSql += "union all \n";
				}
				startCal.add(Calendar.HOUR_OF_DAY, 2);
			}
			
			sql += " RIGHT JOIN ("+tempTableSql+") timetemp on timetemp.timeFlag = TAVG.hour order by timetemp.timeFlag";
		}
		else if(iDataSource == 4){
			while(startCal.getTime().before(endDate)){
				int day = startCal.get(Calendar.DAY_OF_MONTH);
				tempTableSql += "union all \n";
				tempTableSql += " select '"+sf.format(startCal.getTime())+"'tempDate,'"+(day < 10 ?"0"+day:day)+"' timeFlag from dual \n";
				startCal.add(Calendar.DAY_OF_MONTH, 1);
			}
			//tempTableSql += " select '"+sf.format(startCal.getTime())+"'tempDate,'"+startCal.get(Calendar.DAY_OF_MONTH)+"' timeFlag from dual \n";
			tempTableSql=tempTableSql.replaceFirst("union all", "");
			sql += " RIGHT JOIN ("+tempTableSql+") timetemp on timetemp.timeFlag = TAVG.day order by timetemp.tempdate";
		}
		return sql;
	}
	
	
	private String getAmountSql(String wwtp_id, String[] parms, String start, String end) {

		String tableName = "monitor_data_day_amount";

		String sumField = "";
		String selectField = "";
		String getDataSql = "";

		for (int i = 0; i < parms.length; i++) {
			String parmName = parms[i];
			selectField += ",0 AS P" + parmName + "PRE,0 AS P" + parmName + "DENS";

			sumField += ",SUM(P" + parmName + "PRE) AS P" + parmName + "PRE,SUM(P" + parmName + "DENS) AS P" + parmName	+ "DENS";
		}
		selectField = selectField.replaceFirst(",", "") + ", DATATIME";
		sumField = sumField.replaceFirst(",", "") + ", DATATIME";

		for (int i = 0; i < parms.length; i++) {
			String parmName = parms[i];
			String thisSelectField = selectField.replace("0 AS P" + parmName + "PRE", " AMOUNT_PRE AS P" + parmName + "PRE");
			thisSelectField = thisSelectField.replace("0 AS P" + parmName + "DENS", "AMOUNT_DENS AS P" + parmName + "DENS");
			getDataSql += "UNION ALL \r\n  SELECT " + thisSelectField + " FROM ( \r\n"
					+ ("  select AMOUNT_PRE,AMOUNT_DENS,datatime from ")
					+ tableName + " where WWTP_ID='" + wwtp_id + "' AND POLLNAME = '" + parmName
					+ "' order by datatime desc \r\n" + "   ) t WHERE DATATIME >= to_date('" + start
					+ " 00:00:00','YYYY-MM-DD HH24:MI:SS') and DATATIME <= to_date('" + end
					+ " 23:59:59','YYYY-MM-DD HH24:MI:SS') \r\n";
		}
		getDataSql = getDataSql.replaceFirst("UNION ALL", "");
		getDataSql = " SELECT " + sumField + " FROM ( \r\n" + getDataSql
				+ (") t1 GROUP BY DATATIME order by datatime asc");

		return getDataSql;
	}	
	
	/**
	 * 获取报表通用方法
	 * gislin
	 * 2016年3月22日
	 * @param params
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public FourTuple<List<RowBase>, List<RowBase>, ThreeTuple<RowBase, RowBase, RowBase>,TwoTuple<RowBase, RowBase>>  
		getReport(Map<String,String> params) {
			
		String parmToChecks = params.get("parmToChecks");
		String waterType = params.get("water_type");
		String mn = params.get("mn");
		String startDate = params.get("startDate");
		String endDate = params.get("endDate");
		String dataType = params.get("dataType");
		String isCOUStr = params.get("isCOU");
		boolean isCOU = false;
		
		if(mn == null || mn.equals("")){
			return null;
		}
		
		if(isCOUStr != null && isCOUStr.toUpperCase().equals("TRUE")){
			isCOU = true;
		}
		
		String[] parms = parmToChecks.split(",");
		
		TwoTuple<String,String> tuple = getStaticSql(mn, parms, startDate,endDate,dataType,isCOU,waterType);
		
		FourTuple<List<RowBase>, List<RowBase>, ThreeTuple<RowBase, RowBase, RowBase>,TwoTuple<RowBase, RowBase>> rows = getReportRows(tuple.first,tuple.second, waterType,parms,mn,isCOU);
		
		return rows;
	}	
	/**
	 * 获取工业污染源统计报表（包括实测值和折算值）
	 * 熊伟
	 * 2016年12月22日
	 * @param params
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public FourTuple<List<RowBase>, List<RowBase>, ThreeTuple<RowBase, RowBase, RowBase>,TwoTuple<RowBase, RowBase>> getIndustReport(Map<String,String> params) {
			
		String parmToChecks = params.get("parmToChecks");
		String waterType = params.get("water_type");
		String mn = params.get("mn");
		String startDate = params.get("startDate");
		String endDate = params.get("endDate");
		String dataType = params.get("dataType");
		String isCOUStr = params.get("isCOU");
		String SO2Stard =params.get("SO2Stard"); 
		String NOXStard =params.get("NOXStard"); 
		double SO2StardValue=0.0;
		double NOXStardValue=0.0;
		if(!StringUtil.IsNullOrEmpty(SO2Stard)) {
			SO2StardValue = Double.valueOf(SO2Stard);
		}
		if(!StringUtil.IsNullOrEmpty(NOXStard)) {
			NOXStardValue = Double.valueOf(NOXStard);
		}
		boolean isCOU = false;
		
		if(mn == null || mn.equals("")){
			return null;
		}
		
		if(isCOUStr != null && isCOUStr.toUpperCase().equals("TRUE")){
			isCOU = true;
		}
		
		String[] parms = parmToChecks.split(",");
		
		TwoTuple<String,String> tuple = getIndustElectStaticSql(mn, parms, startDate,endDate,dataType,isCOU,waterType,SO2StardValue,NOXStardValue);
		
		FourTuple<List<RowBase>, List<RowBase>, ThreeTuple<RowBase, RowBase, RowBase>,TwoTuple<RowBase, RowBase>> rows = getIndustElectReportRows(tuple.first,tuple.second, waterType,parms,mn,isCOU);
		
		return rows;
	}
	/**
	 * 获取统计报表sql
	 * gislin
	 * 2016年3月14日
	 * @param monitorEquipmentNo
	 * @param parms
	 * @param start
	 * @param end
	 * @param dataType
	 * @param isCOU 是否统计浓度
	 * @return
	 */
	public TwoTuple<String, String> getStaticSql(String monitorEquipmentNo, String[] parms, String start,String end,String dataType,boolean isCOU,String waterType){

		String tableName = "monitor_data_day";
		String reportField = "AVG";
		String fieldToSelected = "AVG";
		
		List<String> parmList = new ArrayList<>(Arrays.asList(parms));
		
		if(isCOU){
			reportField = "COU";
			fieldToSelected = "COU";
		}
		String selectAvgfield = "datatime,PARMNAME,QN,"+fieldToSelected;
		if("mint".equals(dataType)|| "fiveMin".equals(dataType)){
			tableName = "monitor_data_min";
		} else if("hour".equals(dataType) ){
			tableName = "monitor_data_hour";
		}
		else if("day".equals(dataType) ){
			tableName = "monitor_data_hour";
		}		
		else if("quarter".equals(dataType) || "year".equals(dataType) ){
			selectAvgfield = "to_char(DATATIME,'yyyy-mm') datatime,PARMNAME,"+fieldToSelected;
		}
		else if("week".equals(dataType)){
			selectAvgfield = "to_char(DATATIME,'yyyy-mm-dd') datatime,PARMNAME,"+fieldToSelected;
		}
		else if("month".equals(dataType)){
			selectAvgfield = "to_char(DATATIME,'yyyy-mm-dd') datatime,PARMNAME,"+fieldToSelected;
		}
		
		String parmItems = "";
		
		if(!parmList.contains("011")){
			parmList.add("011");
		}
		
		for(String parmName : parmList){
			parmItems += ",'"+parmName+"' P"+parmName+reportField;
		}
		
		parmItems = parmItems.replaceFirst(",", "");
		
		String getDataSql = "SELECT * FROM (\n" +
				"       select * from ( select "+selectAvgfield+" from "+tableName+" t1 \n"
				+ "			where datatime >= to_date('" + start + "','YYYY-MM-DD HH24:MI:SS') AND datatime <= to_date('" + end + "','YYYY-MM-DD HH24:MI:SS') AND MN = '"+monitorEquipmentNo+"' AND FLAG != 'D' ) t\n" + 
				"              pivot(avg("+reportField+") for PARMNAME in ("+parmItems+"))\n" + 
				"       ) TAVG\n where 1=1 order by TAVG.DATATIME DESC";

		String minselectsql = "";
		String maxselectsql = "";
		String avgselectsql = "";
		String datasql = "";
		for (int i = 0; i < parms.length; i++) {
			String parmName = parms[i];
			minselectsql += ",MIN(P" + parmName + reportField+") AS PMINP" + parmName + reportField+" ";
			maxselectsql += ",MAX(P" + parmName + reportField+") AS PMAXP" + parmName + reportField+" ";
			avgselectsql += ",AVG(P" + parmName + reportField+") AS PAVGP" + parmName + reportField+" ";
		}
		minselectsql = minselectsql.replaceFirst(",", "") + ",";
		maxselectsql = maxselectsql.replaceFirst(",", "") + ",";
		avgselectsql = avgselectsql.replaceFirst(",", "");
		datasql = " SELECT " + minselectsql + maxselectsql + avgselectsql + " FROM (" + getDataSql + " )t3";
		
		return new TwoTuple<String,String>(getDataSql,datasql);
	}
	/**
	 * 获取工业污染源统计报表sql
	 * Bear
	 * 2016年12月22日
	 * @param monitorEquipmentNo
	 * @param parms
	 * @param start
	 * @param end
	 * @param dataType
	 * @param isCOU 是否统计浓度
	 * @return
	 */
	public TwoTuple<String, String> getIndustElectStaticSql(String monitorEquipmentNo, String[] parms, String start,String end,String dataType,boolean isCOU,String waterType,double SO2StardValue,double NOXStardValue){

		String tableName = "monitor_data_day";
		String reportField = "AVG";
		String Zs_MinField = "ZS_MIN";
		String Zs_AvgField = "ZS_AVG";
		String Zs_MaxField = "ZS_MAX";
		String fieldToSelected = "AVG";
		
		List<String> parmList = new ArrayList<>(Arrays.asList(parms));
		String selectAvgfield = "datatime,mn,PARMNAME,QN,"+fieldToSelected;
		String selectZS_Avgfield = "datatime,mn,PARMNAME,QN,"+Zs_AvgField;
		if("mint".equals(dataType)|| "fiveMin".equals(dataType)){
			tableName = "monitor_data_min";
		} else if("hour".equals(dataType) ){
			tableName = "monitor_data_hour";
		}
		else if("day".equals(dataType) ){
			if("2".equals(waterType)||"4".equals(waterType)) {
				tableName = "monitor_data_day";
			} else {
				tableName = "monitor_data_hour";
			}
		}		
		else if("quarter".equals(dataType) || "year".equals(dataType) ){
			selectAvgfield = "to_char(DATATIME,'yyyy-mm') datatime,PARMNAME,"+fieldToSelected;
		}
		else if("week".equals(dataType)){
			selectAvgfield = "to_char(DATATIME,'yyyy-mm-dd') datatime,PARMNAME,"+fieldToSelected;
		}
		else if("month".equals(dataType)){
			selectAvgfield = "to_char(DATATIME,'yyyy-mm-dd') datatime,PARMNAME,"+fieldToSelected;
		}
		
		String parmItems = "";
		String zsMaxParmItems="";
		String zsMinParmItems="";
		String zsAvgParmItems="";
		for(String parmName : parmList){
			parmItems += ",'"+parmName+"' P"+parmName+reportField;
			zsMinParmItems += ",'"+parmName+"' P"+parmName+Zs_MinField;
			zsMaxParmItems += ",'"+parmName+"' P"+parmName+Zs_MaxField;
			zsAvgParmItems += ",'"+parmName+"' P"+parmName+Zs_AvgField;	
		}
		
		parmItems = parmItems.replaceFirst(",", "");
		zsMinParmItems = zsMinParmItems.replaceFirst(",", "");
		zsMaxParmItems = zsMaxParmItems.replaceFirst(",", "");
		zsAvgParmItems = zsAvgParmItems.replaceFirst(",", "");
		
		String getDataSql = "SELECT * FROM (\n"
						+"SELECT * FROM (\n" +
						"   select * from ( select "+selectAvgfield+" from "+tableName+" t1 \n"
						+ "	where datatime >= to_date('" + start + "','YYYY-MM-DD HH24:MI:SS') AND datatime <= to_date('" + end + "','YYYY-MM-DD HH24:MI:SS') AND MN = '"+monitorEquipmentNo+"' AND FLAG != 'D' ) t\n" + 
						"   pivot(avg("+reportField+") for PARMNAME in ("+parmItems+"))\n" + 
						"   ) TAVG\n where 1=1 order by TAVG.DATATIME DESC"+") tt ";
		
		String getZS_AVG_DataSql = "("+"SELECT * FROM (\n" +
				"       select * from ( select "+selectZS_Avgfield+" from "+tableName+" t1 \n"
				+ "			where datatime >= to_date('" + start + "','YYYY-MM-DD HH24:MI:SS') AND datatime <= to_date('" + end + "','YYYY-MM-DD HH24:MI:SS') AND MN = '"+monitorEquipmentNo+"' AND FLAG != 'D' ) t\n" + 
				"              pivot(avg("+Zs_AvgField+") for PARMNAME in ("+zsAvgParmItems+"))\n" + 
				"       ) TAVG\n where 1=1 order by TAVG.DATATIME DESC"+") t3 ";
		
		String ZsSql = 	getDataSql+
						"left join "+getZS_AVG_DataSql+"on tt.datatime = t3.datatime and tt.mn=t3.mn\n";

		String minselectsql = "";
		String maxselectsql = "";
		String avgselectsql = "";
		String zsminselectsql = "";
		String zsmaxselectsql = "";
		String zsavgselectsql = "";
		String datasql = "";
		for (int i = 0; i < parms.length; i++) {
			String parmName = parms[i];
			minselectsql += ",MIN(P" + parmName + reportField+") AS PMINP" + parmName + reportField+" ";
			maxselectsql += ",MAX(P" + parmName + reportField+") AS PMAXP" + parmName + reportField+" ";
			avgselectsql += ",AVG(P" + parmName + reportField+") AS PAVGP" + parmName + reportField+" ";
			zsminselectsql += ",MIN(P" + parmName + reportField+") AS PMINP" + parmName + reportField+" ";
			zsmaxselectsql += ",MAX(P" + parmName + reportField+") AS PMAXP" + parmName + reportField+" ";
			zsavgselectsql += ",AVG(P" + parmName + reportField+") AS PAVGP" + parmName + reportField+" ";
		}
		minselectsql = minselectsql.replaceFirst(",", "") + ",";
		maxselectsql = maxselectsql.replaceFirst(",", "") + ",";
		avgselectsql = avgselectsql.replaceFirst(",", "");
		datasql = " SELECT " + minselectsql + maxselectsql + avgselectsql + " FROM (" + getDataSql + " )t3";
		
		return new TwoTuple<String,String>(ZsSql,datasql);
	}
	/**
	 * 获取报表行
	 * @param dataSql 数据sql
	 * @param statSql 统计sql
	 * @param waterType 站点类型：污染源，地表水站
	 * @param parms 要查询的参数数组
	 * @return
	 */
	private FourTuple<List<RowBase>, List<RowBase>, ThreeTuple<RowBase, RowBase, RowBase>,TwoTuple<RowBase, RowBase>> 
						getReportRows(String dataSql,String statSql, String waterType,String[] parms,String mn,boolean isCOU) {
		
		List<RowBase> rows = DataUtil.getSelectRows(dataSql, null);
		List<RowBase> statisticRows = new ArrayList<>();
		ThreeTuple<RowBase,RowBase,RowBase> waterClassRows = null;
		TwoTuple<RowBase,RowBase> waterComprehensiveIndexRows = null;
		
		//计算统计值
		RowBase minData = new RowBase();
		minData.put("DATATIME", "最小值");
		RowBase maxData = new RowBase();
		maxData.put("DATATIME", "最大值");
		RowBase avgData = new RowBase();
		avgData.put("DATATIME", "平均值");
		
		//如果是通量的话，不统计这些
		if(!isCOU){
			List<RowBase> result = DataUtil.getSelectRows(statSql, null, null);
			
			if (result!=null && result.size() > 0) {
				RowBase statisticRow = result.get(0);
				
				for (String key : statisticRow.keySet()) {
					String value = statisticRow.getSafeString(key);
					if(key.startsWith("PMIN")){
						minData.put(key.substring(4, key.length()), value);
					}
					else if(key.startsWith("PMAX")){
						maxData.put(key.substring(4, key.length()), value);
					}
					else if(key.startsWith("PAVG")){
						avgData.put(key.substring(4, key.length()), value);
					}
				}
				statisticRows.add(minData);
				statisticRows.add(maxData);
				statisticRows.add(avgData);
			}
			statisticRows = NumPointFormat(statisticRows,parms,waterType);//格式化小数点
			
			if(StringUtil.IsNotNullOrEmpty(waterType)){
				if("1".equals(waterType)||"7".equals(waterType)){
					//地表水
					Map<String,String> params = new HashMap<>();
					params.put("category", "SURFACE_WATER");
					List<RowBase> standardRows = getWaterQualityStandards(params);
					
					waterClassRows = getWaterQualityStandardRows(avgData, standardRows,parms);
					waterComprehensiveIndexRows = getWaterPollutionComprehensiveIndexRows(avgData, standardRows);
				}
			}
		}
		
		rows = NumPointFormat(rows,parms,waterType);//格式化小数点
		rows = GenerateAlarmInfo(rows, parms, mn);
		
		return new FourTuple<List<RowBase>, List<RowBase>, ThreeTuple<RowBase, RowBase, RowBase>,TwoTuple<RowBase, RowBase>> (rows,statisticRows,waterClassRows,waterComprehensiveIndexRows);
	}
	/**
	 * 获取工业污染源电厂报表行
	 * @param dataSql 数据sql
	 * @param statSql 统计sql
	 * @param waterType 站点类型：污染源，工业污染源电厂站点
	 * @param parms 要查询的参数数组
	 * @return
	 */
	private FourTuple<List<RowBase>, List<RowBase>, ThreeTuple<RowBase, RowBase, RowBase>,TwoTuple<RowBase, RowBase>> 
						getIndustElectReportRows(String dataSql,String statSql, String waterType,String[] parms,String mn,boolean isCOU) {
		
		List<RowBase> rows = DataUtil.getSelectRows(dataSql, null);
		List<RowBase> statisticRows = new ArrayList<>();
		ThreeTuple<RowBase,RowBase,RowBase> waterClassRows = null;
		TwoTuple<RowBase,RowBase> waterComprehensiveIndexRows = null;
		
		//计算统计值
		RowBase minData = new RowBase();
		minData.put("DATATIME", "最小值");
		RowBase maxData = new RowBase();
		maxData.put("DATATIME", "最大值");
		RowBase avgData = new RowBase();
		avgData.put("DATATIME", "平均值");
		
		//如果是通量的话，不统计这些
		if(!isCOU){
			List<RowBase> result = DataUtil.getSelectRows(statSql, null, null);
			
			if (result!=null && result.size() > 0) {
				RowBase statisticRow = result.get(0);
				
				for (String key : statisticRow.keySet()) {
					String value = statisticRow.getSafeString(key);
					if(key.startsWith("PMIN")){
						minData.put(key.substring(4, key.length()), value);
					}
					else if(key.startsWith("PMAX")){
						maxData.put(key.substring(4, key.length()), value);
					}
					else if(key.startsWith("PAVG")){
						avgData.put(key.substring(4, key.length()), value);
					}
				}
				statisticRows.add(minData);
				statisticRows.add(maxData);
				statisticRows.add(avgData);
			}
			statisticRows = NumPointFormat(statisticRows,parms,waterType);//格式化小数点
			
			if(StringUtil.IsNotNullOrEmpty(waterType)){
				if("1".equals(waterType)||"7".equals(waterType)){
					//地表水
					Map<String,String> params = new HashMap<>();
					params.put("category", "SURFACE_WATER");
					List<RowBase> standardRows = getWaterQualityStandards(params);
					
					waterClassRows = getWaterQualityStandardRows(avgData, standardRows,parms);
					waterComprehensiveIndexRows = getWaterPollutionComprehensiveIndexRows(avgData, standardRows);
				}
			}
		}
		
		rows = NumPointFormat(rows,parms,waterType);//格式化小数点
		rows = GenerateAlarmInfo(rows, parms, mn);
		
		return new FourTuple<List<RowBase>, List<RowBase>, ThreeTuple<RowBase, RowBase, RowBase>,TwoTuple<RowBase, RowBase>> (rows,statisticRows,waterClassRows,waterComprehensiveIndexRows);
	}

	/**
	 * 获取水质类别行
	 * @param avgrow
	 * @param standardRows
	 * @return
	 */
	private ThreeTuple<RowBase,RowBase,RowBase> getWaterQualityStandardRows(RowBase avgrow,List<RowBase> standardRows,String[] parms){
		
		RowBase itemRow = new RowBase();//分项类别
		RowBase totalRow = new RowBase();//综合水质类别
		RowBase mainPollutantRow = new RowBase();//主要污染物
		
		itemRow.put("DATATIME", "分类类别");
		totalRow.put("DATATIME", "综合水质类别");
		mainPollutantRow.put("DATATIME", "主要污染物");
		
		HashMap<String,String> parmsMap = new HashMap<>();
		for(String parm:parms){
			parmsMap.put(parm.toUpperCase(), parm);
		}
		
		int maxQuality = 0;
		String totalClassDesc = "";
		String mainPollutant = "";
		for (String field : avgrow.keySet()) {
			if (!"DATATIME".equals(field) && !RowBase.isSystemField(field)) {
				TwoTuple<Integer,String> tuple = getItemClass(avgrow,field,standardRows);
				
				String itemClassDesc = "";
				
				if(tuple!=null){
					int itemClass = tuple.first;
					
					if(itemClass==1){
						itemClassDesc = "I";
					}else if(itemClass==2){
						itemClassDesc = "II";
					}else if(itemClass==3){
						itemClassDesc = "III";
					}else if(itemClass==4){
						itemClassDesc = "IV";
					}else if(itemClass==5){
						itemClassDesc = "V";
					}
					else{
						itemClassDesc = "劣V";
					}
					
					if(itemClass>maxQuality){
						maxQuality=itemClass;
						totalClassDesc = itemClassDesc;
					}
					if(itemClass>=4){
						String pollCode = parmsMap.get(field.replaceFirst("P", "").replace("AVG", ""));
						String pollName = RedisHelper.getInstance().translationDictValue("MONITOR_PARAM",pollCode);
						mainPollutant+=pollName+",";
					}
				}
				
				itemRow.put(field, itemClassDesc);
			}
		}
		
		totalRow.put("TOTAL_CLASS", String.valueOf(totalClassDesc));
		
		if(mainPollutant.length()>0){
			mainPollutant=mainPollutant.substring(0,mainPollutant.length()-1);
		}
		
		mainPollutantRow.put("MAIN_POLLUTANT", mainPollutant);
		
		return new ThreeTuple<RowBase,RowBase,RowBase>(itemRow,totalRow,mainPollutantRow);
	}
	/**
	 * 获取综合污染指数行
	 * @param avgrow 均值
	 * @param standardRows 标准值
	 * @return
	 */
	private TwoTuple<RowBase,RowBase> getWaterPollutionComprehensiveIndexRows (RowBase avgrow,List<RowBase> standardRows) {
		RowBase comprehensivePolluteIndexRow = new RowBase();//综合污染指数
		comprehensivePolluteIndexRow.put("DATATIME", "水质情况（综合污染数值）");
		String waterConditon = "";
		double ComprehensiveIndex=0.0;
		//计算综合污染指数
			ComprehensiveIndex = computeComprehensiveIndex(avgrow,standardRows);
		//污染指标保留两位小数
			if (ComprehensiveIndex > 0) {
				BigDecimal result = new BigDecimal(ComprehensiveIndex);
				double polluteIndex = result.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
				//遍历标准，遍历到综合污染指数标准
					
					for(RowBase r:standardRows){
						if((r.getSafeString("ITEM")).equals("POLL_INDEX")){
							if(polluteIndex >= r.getSafeDouble("MINIMUM") && polluteIndex <= r.getSafeDouble("MAXIMUM")){
								waterConditon = r.getSafeString("REMARK")+"("+polluteIndex+")";
								break;
							}
						}
					}
			}
			comprehensivePolluteIndexRow.put("POLLUTE_INDEX",waterConditon);
		return new TwoTuple<RowBase,RowBase>(null,comprehensivePolluteIndexRow);
	}
	/**
	 * 获取水质类别行
	 * @param avgrow 均值
	 * @param standardRows 标准值
	 * @return
	 */
	private double computeComprehensiveIndex (RowBase avgrow,List<RowBase> standardRows) {
	/*目前只计算五个指标的综合污染指数；其中，PH和DO(溶解氧)的计算公式不一样*/		
		/*首先计算COD,氨氮，总磷各自的污染分指数Pi。*/
		double result = 0.0;
		double COD_Pi = 0;
		double NH4_Pi = 0;
		double P_Pi = 0;
		double PH_Pi = 0;
		double Do_Pi = 0;
		RowBase avgRow = avgrow;
		double phtem = avgrow.getSafeDouble("PPHTEMAVG");
		for (String field : avgrow.keySet()) {	
			if (!"DATATIME".equals(field) && !RowBase.isSystemField(field)) {
				if(StringUtil.IsNotNullOrEmpty(avgRow.getSafeString(field)) && field.endsWith("AVG") && ("P011AVG").equals(field)){//浓度
					COD_Pi = avgRow.getSafeDouble(field)/40;
				}
				if(StringUtil.IsNotNullOrEmpty(avgRow.getSafeString(field)) && field.endsWith("AVG") && ("P060AVG").equals(field)){//浓度
					NH4_Pi = avgRow.getSafeDouble(field)/2;
				}
				if(StringUtil.IsNotNullOrEmpty(avgRow.getSafeString(field)) && field.endsWith("AVG") && ("P101AVG").equals(field)){//浓度
					P_Pi = avgRow.getSafeDouble(field)/2;
				}
				if(StringUtil.IsNotNullOrEmpty(avgRow.getSafeString(field)) && field.endsWith("AVG") && ("P001AVG").equals(field)){//浓度
					double phValue = avgRow.getSafeDouble(field);
					if(phValue>7.0) {
						PH_Pi = (phValue-7)/2;
					} else if(phValue<=7.0){
						PH_Pi = 7.0-phValue;
					}
				}
				if(StringUtil.IsNotNullOrEmpty(avgRow.getSafeString(field)) && field.endsWith("AVG") && ("PDOAVG").equals(field)){//浓度
					double doTestValue = avgRow.getSafeDouble(field);
					double DOf = 468/(31.6+phtem);
					double DOs = 2.0;
					if(doTestValue>=DOs) {
						Do_Pi = Math.abs(DOf-doTestValue)/(DOf-DOs);
					} else {
						Do_Pi = 10-9*(doTestValue/DOs);
					}
				}
			}
		}
		result = COD_Pi+NH4_Pi+P_Pi+PH_Pi+Do_Pi;
		return result;
	}
	/**
	 * 计算污染分指数
	 * @param avgRow
	 * @param field
	 * @param standardRows
	 * @return
	 */
	private TwoTuple<Integer,String> computePartIndex (RowBase avgRow,String field,List<RowBase> standardRows) {
		TwoTuple<Integer,String> itemClass = null;
		if(StringUtil.IsNotNullOrEmpty(avgRow.getSafeString(field)) && field.endsWith("AVG")){//浓度
			double value = avgRow.getSafeDouble(field);
			
				for(RowBase r:standardRows){
					if(("P"+r.getSafeString("ITEM")+"AVG").equals(field)){
						itemClass = new TwoTuple<Integer,String>(6,"");
						if(value>=r.getSafeDouble("MINIMUM") && value<=r.getSafeDouble("MAXIMUM")){
							int qualityClass = r.getSafeInt("QUALITY_CLASS");
							itemClass = new TwoTuple<Integer,String>(qualityClass,"");
							break;
						}
					}
				}
		}
		
		return itemClass;
	}
	/**
	 * 获取分项类别行
	 * @param avgRow
	 * @param field
	 * @param standardRows
	 * @return
	 */
	private TwoTuple<Integer,String> getItemClass(RowBase avgRow,String field,List<RowBase> standardRows){
		TwoTuple<Integer,String> itemClass = null;
		
		if(StringUtil.IsNotNullOrEmpty(avgRow.getSafeString(field)) && field.endsWith("AVG")){//浓度
			double value = avgRow.getSafeDouble(field);
			
				for(RowBase r:standardRows){
					if(("P"+r.getSafeString("ITEM")+"AVG").equals(field)){
						itemClass = new TwoTuple<Integer,String>(6,"");
						if(value>=r.getSafeDouble("MINIMUM") && value<=r.getSafeDouble("MAXIMUM")){
							int qualityClass = r.getSafeInt("QUALITY_CLASS");
							itemClass = new TwoTuple<Integer,String>(qualityClass,"");
							break;
						}
					}
				}
		}
		
		
		return itemClass;
	}

	/**
	 * 生成排放量的市级日报表
	 * @param date
	 * @param param
	 * @param flowType
	 * @return
	 */
	public Map<String, RowBase> generateCityDayReport(String date, String param,String flowType,String waterType) {
		List<RowBase> listData = getCityDayList(date, param,flowType,waterType);

		// 生成表头
		List<RowBase> headers = getDayHeaders(true);

		String xzq = "";
		List<RowBase> xzqhs = domainService.getXZQHDomain();
		Map<String, String> xzqMap = new HashMap<>();
		for (RowBase r : xzqhs) {
			xzq = r.getSafeString("S_DOMAINCODE");
			xzqMap.put(xzq, r.getSafeString("S_DOMAINCAPTION"));
		}

		Map<String, RowBase> result = new LinkedHashMap<String, RowBase>();
		RowBase row = null;
		String day = null;
		double amount = 0d;
		for (RowBase r : listData) {
			xzq = r.getSafeString("XZQ");
			if (result.containsKey(xzq)) {
				row = result.get(xzq);
			} else {
				row = new RowBase();
				for (RowBase col : headers) {
					row.put(col.getSafeString("COLUMN"), null);
				}
				row.put("CODE", xzqMap.get(xzq) == null ? xzq : xzqMap.get(xzq));
				result.put(xzq, row);
			}

			day = r.getSafeString("DAYCOL");
			amount = r.getSafeDouble("AMOUNT");
			row.put("COL" + day, amount);
		}

		// 处理合计列
		for (RowBase r : result.values()) {
			amount = 0d;
			for (String col : r.keySet()) {
				if ("TOTAL".equals(col) || RowBase.FIELD_ISNEW.equals(col))
					continue;

				amount += r.getSafeDouble(col);
			}
			BigDecimal b = new BigDecimal(amount);  
			amount = b.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();
			r.put("TOTAL", amount);
		}

		// 处理合计行
		if(result.size()>0){
			RowBase totalRow = new RowBase();
			totalRow.put("CODE", "合计");
			for (RowBase col : headers) {
				if ("CODE".equals(col.getSafeString("COLUMN")))
					continue;
	
				amount = 0d;
				for (RowBase r : result.values()) {
					amount += r.getSafeDouble(col.getSafeString("COLUMN"));
				}
				BigDecimal b = new BigDecimal(amount);  
				amount = b.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();
				totalRow.put(col.getSafeString("COLUMN"), amount);
			}
			result.put("TOTAL", totalRow);
		}
		return result;
	}
	
	/**
	 * 获取日报表表头
	 * @param year
	 * @param month
	 * @return
	 */
	public List<RowBase> getDayHeaders(boolean isCity) {
		List<RowBase> headers = new ArrayList<>();
		RowBase xzqHeader = new RowBase();
		if(isCity){
			xzqHeader.put("COLUMN", "CODE");
			xzqHeader.put("HEADER", "行政区划");
		}else{
			xzqHeader.put("COLUMN", "CODE");
			xzqHeader.put("HEADER", "企业名称");
		}
		headers.add(xzqHeader);
		headers.addAll(getDayHeader());
		RowBase totalHeader = new RowBase();
		totalHeader.put("COLUMN", "TOTAL");
		totalHeader.put("HEADER", "总计");
		headers.add(totalHeader);
		return headers;
	}

	/**
	 * 获取日报表表头
	 * @return
	 */
	private List<RowBase> getDayHeader() {
		List<RowBase> headers = new ArrayList<>();
		// 各个日期的列名和表头
		String key = null;
		for (int i = 0; i <= 23; i++) {
			key = Integer.toString(i);
			if (i < 10) {
				key = "0" + key;
			}
			key = "COL" + key;
	
			RowBase rowHeader = new RowBase();
			rowHeader.put("COLUMN", key);
			rowHeader.put("HEADER", i + "时");
			headers.add(rowHeader);
		}
	
		return headers;
	}

	/**
	 * 获取市级日报表数据
	 * @param date
	 * @param param
	 * @param flowType
	 * @param waterType
	 * @return
	 */
	private List<RowBase> getCityDayList(String date, String param,String flowType,String waterType) {
	
		String sql = "select  k.xzq,k.DAYCOL, round(sum(k.cou), 2) as AMOUNT" 
				+ " from (select t.cou,"
				+ "          to_char(t.datatime, 'hh24') as DAYCOL," 
				+ "          substr(dci.site_id, 3, 2) as XZQ"
				+ "     from monitor_data_hour t"
				+ "     left join BC_COLLECTION_INFO dci on t.mn = dci.device_num" 
				+ "     left join BC_SITE_INFO ms on dci.site_id = ms.code"
				+ "    where t.parmname='"	+ param + "'" ;
		
		if(StringUtil.IsNotNullOrEmpty(flowType)){
			sql+= "      and ms.flow_type = '"+flowType+"'" ;
		}
		if(StringUtil.IsNotNullOrEmpty(waterType)){
			sql+= "      and ms.STATION_CATEGORY = '"+waterType+"'" ;
		}
		
		sql+= "      and t.datatime >= to_date('" + date + " 00:00:00', 'yyyy-MM-dd HH24:MI:SS')"
				+ "      and t.datatime <= to_date('" + date + " 23:59:59', 'yyyy-MM-dd HH24:MI:SS')"
				+ "    order by t.datatime) k" + "  group by k.daycol, k.xzq order by k.xzq,k.daycol";
	
		List<RowBase> listData = DataUtil.getSelectRows(sql, null);
		return listData;
	}

	/**
	 * 获取区级日报表数据
	 * @param region
	 * @param date
	 * @param param
	 * @param flowType
	 * @param waterType
	 * @return
	 */
	public Map<String, RowBase> generateRegionDayReport(String region, String date, String param,String flowType,String waterType) {
		List<RowBase> listData = getRegionDayList(region, date, param,flowType,waterType);
		// 生成表头
		List<RowBase> headers = getDayHeaders(false);
	
		String wwtpCode = "";
		Map<String, RowBase> result = new LinkedHashMap<String, RowBase>();
		RowBase row = null;
		String day = null;
		double amount = 0d;
		for (RowBase r : listData) {
			wwtpCode = r.getSafeString("WWTPCODE");
			if (result.containsKey(wwtpCode)) {
				row = result.get(wwtpCode);
			} else {
				row = new RowBase();
				for (RowBase col : headers) {
					row.put(col.getSafeString("COLUMN"), null);
				}
				row.put("CODE", r.getSafeString("WWTPNAME"));
				result.put(wwtpCode, row);
			}
	
			day = r.getSafeString("DAYCOL");
			amount = r.getSafeDouble("AMOUNT");
			row.put("COL" + day, amount);
		}
	
		// 处理合计列
		for (RowBase r : result.values()) {
			amount = 0d;
			for (String col : r.keySet()) {
				if ("TOTAL".equals(col) || RowBase.FIELD_ISNEW.equals(col))
					continue;
	
				amount += r.getSafeDouble(col);
			}
			BigDecimal b = new BigDecimal(amount);  
			amount = b.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();
			r.put("TOTAL", amount);
		}
	
		// 处理合计行
		if(result.size()>0){
			RowBase totalRow = new RowBase();
			totalRow.put("CODE", "合计");
			for (RowBase col : headers) {
				if ("CODE".equals(col.getSafeString("COLUMN")))
					continue;
	
				amount = 0d;
				for (RowBase r : result.values()) {
					amount += r.getSafeDouble(col.getSafeString("COLUMN"));
				}
				BigDecimal b = new BigDecimal(amount);  
				amount = b.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();
				totalRow.put(col.getSafeString("COLUMN"), amount);
			}
			result.put("TOTAL", totalRow);
		}
		return result;
	}

	/**
	 * 获取区级日报表数据
	 * @param region
	 * @param date
	 * @param param
	 * @param flowType
	 * @param waterType
	 * @return
	 */
	private List<RowBase> getRegionDayList(String region, String date, String param,String flowType,String waterType) {
	
		String sql = "select k.WWTPCODE,k.WWTPNAME,k.DAYCOL, round(sum(k.cou), 2) as AMOUNT"
				+ " from (select to_char(t.datatime, 'hh24') as DAYCOL,"
				+ "             ms.code as STATIONCODE,ms.name as STATIONNAME,"
				+ "             p.code as WWTPCODE,p.name as WWTPNAME,t.cou" 
				+ "        from monitor_data_hour t"
				+ "        left join BC_COLLECTION_INFO dci on t.mn = dci.device_num"
				+ "       left join BC_SITE_INFO ms on dci.site_id = ms.code"
				+ "       left join BC_ENTERPRISE_ATTR p on ms.wwtp_id = p.code" 
				+ "      where t.parmname='"+param+"'";
		
		if(StringUtil.IsNotNullOrEmpty(flowType)){
			sql+= "      and ms.flow_type = '"+flowType+"'" ;
		}
		if(StringUtil.IsNotNullOrEmpty(waterType)){
			sql+= "      and ms.STATION_CATEGORY = '"+waterType+"'" ;
		}
				
		sql+= "      and t.datatime >= to_date('" + date + " 00:00:00', 'yyyy-MM-dd HH24:MI:SS')" 
				+ "      and t.datatime <= to_date('" + date	+ " 23:59:59', 'yyyy-MM-dd HH24:MI:SS')" 
				+ "       and dci.site_id like '__" + region + "%'"
				+ "       order by t.datatime) k"
				+ " group by k.WWTPCODE,k.WWTPNAME,k.daycol order by k.WWTPCODE,k.WWTPNAME,k.daycol";
	
		List<RowBase> listData = DataUtil.getSelectRows(sql, null);
		return listData;
	}

	/**
	 * 获取市级月报表
	 * @param year
	 * @param month
	 * @param param
	 * @param flowType
	 * @param waterType
	 * @return
	 */
	public Map<String, RowBase> generateCityMonthReport(String year, String month, String param,String flowType,String waterType) {
		List<RowBase> listData = getCityMonthList(year, month, param,flowType,waterType);

		// 生成表头
		List<RowBase> headers = getMonthHeaders(year, month,true);

		String xzq = "";
		List<RowBase> xzqhs = domainService.getXZQHDomain();
		Map<String, String> xzqMap = new HashMap<>();
		for (RowBase r : xzqhs) {
			xzq = r.getSafeString("S_DOMAINCODE");
			xzqMap.put(xzq, r.getSafeString("S_DOMAINCAPTION"));
		}

		Map<String, RowBase> result = new LinkedHashMap<String, RowBase>();
		RowBase row = null;
		String day = null;
		double amount = 0d;
		for (RowBase r : listData) {
			xzq = r.getSafeString("XZQ");
			if (result.containsKey(xzq)) {
				row = result.get(xzq);
			} else {
				row = new RowBase();
				for (RowBase col : headers) {
					row.put(col.getSafeString("COLUMN"), null);
				}
				row.put("CODE", xzqMap.get(xzq) == null ? xzq : xzqMap.get(xzq));
				result.put(xzq, row);
			}

			day = r.getSafeString("DAYCOL");
			amount = r.getSafeDouble("AMOUNT");
			row.put("COL" + day, amount);
		}

		// 处理合计列
		for (RowBase r : result.values()) {
			amount = 0d;
			for (String col : r.keySet()) {
				if ("TOTAL".equals(col) || RowBase.FIELD_ISNEW.equals(col))
					continue;

				amount += r.getSafeDouble(col);
			}
			BigDecimal b = new BigDecimal(amount);  
			amount = b.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();
			r.put("TOTAL", amount);
		}

		// 处理合计行
		if(result.size()>0){
			RowBase totalRow = new RowBase();
			totalRow.put("CODE", "合计");
			for (RowBase col : headers) {
				if ("CODE".equals(col.getSafeString("COLUMN")))
					continue;
	
				amount = 0d;
				for (RowBase r : result.values()) {
					amount += r.getSafeDouble(col.getSafeString("COLUMN"));
				}
				BigDecimal b = new BigDecimal(amount);  
				amount = b.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();
				totalRow.put(col.getSafeString("COLUMN"), amount);
			}
			result.put("TOTAL", totalRow);
		}
		return result;
	}

	/**
	 * 获取月报表表头
	 * @param year
	 * @param month
	 * @param isCity
	 * @return
	 */
	public List<RowBase> getMonthHeaders(String year, String month, boolean isCity) {
		List<RowBase> headers = new ArrayList<>();
		RowBase xzqHeader = new RowBase();
		if(isCity){
			xzqHeader.put("COLUMN", "CODE");
			xzqHeader.put("HEADER", "行政区划");
		}else{
			xzqHeader.put("COLUMN", "CODE");
			xzqHeader.put("HEADER", "企业名称");
		}
		headers.add(xzqHeader);
		headers.addAll(getMonthHeader(year, month));
		RowBase totalHeader = new RowBase();
		totalHeader.put("COLUMN", "TOTAL");
		totalHeader.put("HEADER", "总计");
		headers.add(totalHeader);
		return headers;
	}
	
	/**
	 * 获取月报表表头
	 * @param year
	 * @param month
	 * @return
	 */
	private List<RowBase> getMonthHeader(String year, String month) {
		int dayCount = DateHelper.getDayCountOfMonth(Integer.parseInt(year), Integer.parseInt(month));
		List<RowBase> headers = new ArrayList<>();
		// 各个日期的列名和表头
		String key = null;
		for (int i = 1; i <= dayCount; i++) {
			key = Integer.toString(i);
			if (i < 10) {
				key = "0" + key;
			}
			key = "COL" + key;

			RowBase rowHeader = new RowBase();
			rowHeader.put("COLUMN", key);
			rowHeader.put("HEADER", i + "日");
			headers.add(rowHeader);
		}

		return headers;
	}

	/**
	 * 获取市报表列表
	 * @param year
	 * @param month
	 * @param param
	 * @param flowType
	 * @param waterType
	 * @return
	 */
	private List<RowBase> getCityMonthList(String year, String month, String param,String flowType,String waterType) {

		int iYear = Integer.parseInt(year);
		int iMonth = Integer.parseInt(month);
		String start = DateHelper.getFirstDayOfMonth(iYear, iMonth);
		String end = DateHelper.getLastDayOfMonth(iYear, iMonth);

		String sql = "select  k.xzq,k.DAYCOL, round(sum(k.cou), 2) as AMOUNT" 
				+ " from (select t.cou,"
				+ "          to_char(t.datatime, 'dd') as DAYCOL," 
				+ "          substr(dci.site_id, 3, 2) as XZQ"
				+ "     from monitor_data_day t"
				+ "     left join BC_COLLECTION_INFO dci on t.mn = dci.device_num" 
				+ "     left join BC_SITE_INFO ms on dci.site_id = ms.code"
				+ "    where t.parmname='"	+ param + "'" ;
		
		if(StringUtil.IsNotNullOrEmpty(flowType)){
			sql+= "      and ms.flow_type = '"+flowType+"'" ;
		}
		if(StringUtil.IsNotNullOrEmpty(waterType)){
			sql+= "      and ms.STATION_CATEGORY = '"+waterType+"'" ;
		}
		
		sql+= "      and t.datatime >= to_date('" + start + " 00:00:00', 'yyyy-MM-dd HH24:MI:SS')"
				+ "      and t.datatime <= to_date('" + end + " 23:59:59', 'yyyy-MM-dd HH24:MI:SS')"
				+ "    order by t.datatime) k" + "  group by k.daycol, k.xzq order by k.xzq,k.daycol";

		List<RowBase> listData = DataUtil.getSelectRows(sql, null);
		return listData;
	}

	/**
	 * 获取区域月报表
	 * @param region
	 * @param year
	 * @param month
	 * @param param
	 * @param flowType
	 * @param waterType
	 * @return
	 */
	public Map<String, RowBase> generateRegionMonthReport(String region, String year, String month, String param,String flowType,String waterType) {
		List<RowBase> listData = getRegionMonthList(region, year, month, param,flowType,waterType);
		// 生成表头
		List<RowBase> headers = getMonthHeaders(year, month,false);

		String wwtpCode = "";
		Map<String, RowBase> result = new LinkedHashMap<String, RowBase>();
		RowBase row = null;
		String day = null;
		double amount = 0d;
		for (RowBase r : listData) {
			wwtpCode = r.getSafeString("WWTPCODE");
			if (result.containsKey(wwtpCode)) {
				row = result.get(wwtpCode);
			} else {
				row = new RowBase();
				for (RowBase col : headers) {
					row.put(col.getSafeString("COLUMN"), null);
				}
				row.put("CODE", r.getSafeString("WWTPNAME"));
				result.put(wwtpCode, row);
			}

			day = r.getSafeString("DAYCOL");
			amount = r.getSafeDouble("AMOUNT");
			row.put("COL" + day, amount);
		}

		// 处理合计列
		for (RowBase r : result.values()) {
			amount = 0d;
			for (String col : r.keySet()) {
				if ("TOTAL".equals(col) || RowBase.FIELD_ISNEW.equals(col))
					continue;

				amount += r.getSafeDouble(col);
			}
			BigDecimal b = new BigDecimal(amount);  
			amount = b.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();
			r.put("TOTAL", amount);
		}

		// 处理合计行
		if(result.size()>0){
			RowBase totalRow = new RowBase();
			totalRow.put("CODE", "合计");
			for (RowBase col : headers) {
				if ("CODE".equals(col.getSafeString("COLUMN")))
					continue;
	
				amount = 0d;
				for (RowBase r : result.values()) {
					amount += r.getSafeDouble(col.getSafeString("COLUMN"));
				}
				BigDecimal b = new BigDecimal(amount);  
				amount = b.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();
				totalRow.put(col.getSafeString("COLUMN"), amount);
			}
			result.put("TOTAL", totalRow);
		}
		return result;
	}

	/**
	 * 获取区域月报表数据
	 * @param region
	 * @param year
	 * @param month
	 * @param param
	 * @param flowType
	 * @param waterType
	 * @return
	 */
	private List<RowBase> getRegionMonthList(String region, String year, String month, String param,String flowType,String waterType) {

		int iYear = Integer.parseInt(year);
		int iMonth = Integer.parseInt(month);
		String start = DateHelper.getFirstDayOfMonth(iYear, iMonth);
		String end = DateHelper.getLastDayOfMonth(iYear, iMonth);

		String sql = "select k.WWTPCODE,k.WWTPNAME,k.DAYCOL, round(sum(k.cou), 2) as AMOUNT"
				+ " from (select to_char(t.datatime, 'dd') as DAYCOL,"
				+ "             ms.code as STATIONCODE,ms.name as STATIONNAME,"
				+ "             p.code as WWTPCODE,p.name as WWTPNAME,t.cou" 
				+ "        from monitor_data_day t"
				+ "        left join BC_COLLECTION_INFO dci on t.mn = dci.device_num"
				+ "       left join BC_SITE_INFO ms on dci.site_id = ms.code"
				+ "       left join BC_ENTERPRISE_ATTR p on ms.wwtp_id = p.code" 
				+ "      where t.parmname='"+param+"'";
		
		if(StringUtil.IsNotNullOrEmpty(flowType)){
			sql+= "      and ms.flow_type = '"+flowType+"'" ;
		}
		if(StringUtil.IsNotNullOrEmpty(waterType)){
			sql+= "      and ms.STATION_CATEGORY = '"+waterType+"'" ;
		}
				
		sql+= "      and t.datatime >= to_date('" + start + " 00:00:00', 'yyyy-MM-dd HH24:MI:SS')" 
				+ "      and t.datatime <= to_date('" + end	+ " 23:59:59', 'yyyy-MM-dd HH24:MI:SS')" 
				+ "       and dci.site_id like '__" + region + "%'"
				+ "       order by t.datatime) k"
				+ " group by k.WWTPCODE,k.WWTPNAME,k.daycol order by k.WWTPCODE,k.WWTPNAME,k.daycol";

		List<RowBase> listData = DataUtil.getSelectRows(sql, null);
		return listData;
	}

	/**
	 * 获取市级季度报表
	 * @param year
	 * @param quarter
	 * @param param
	 * @param flowType
	 * @param waterType
	 * @return
	 */
	public Map<String, RowBase> generateCityQuarterReport(String year, String quarter, String param,String flowType,String waterType) {
		
		List<RowBase> listData = getCityQuarterList(year, quarter, param,flowType,waterType);
		
		// 生成表头
		List<RowBase> headers = getQuarterHeaders(year, quarter,true);
	
		String xzq = "";
		List<RowBase> xzqhs = domainService.getXZQHDomain();
		Map<String, String> xzqMap = new HashMap<>();
		for (RowBase r : xzqhs) {
			xzq = r.getSafeString("S_DOMAINCODE");
			xzqMap.put(xzq, r.getSafeString("S_DOMAINCAPTION"));
		}
	
		Map<String, RowBase> result = new LinkedHashMap<String, RowBase>();
		RowBase row = null;
		String day = null;
		double amount = 0d;
		for (RowBase r : listData) {
			xzq = r.getSafeString("XZQ");
			if (result.containsKey(xzq)) {
				row = result.get(xzq);
			} else {
				row = new RowBase();
				for (RowBase col : headers) {
					row.put(col.getSafeString("COLUMN"), null);
				}
				row.put("CODE", xzqMap.get(xzq) == null ? xzq : xzqMap.get(xzq));
				result.put(xzq, row);
			}
	
			day = r.getSafeString("DAYCOL");
			amount = r.getSafeDouble("AMOUNT");
			row.put("COL" + day, amount);
		}
	
		// 处理合计列
		for (RowBase r : result.values()) {
			amount = 0d;
			for (String col : r.keySet()) {
				if ("TOTAL".equals(col) || RowBase.FIELD_ISNEW.equals(col))
					continue;
	
				amount += r.getSafeDouble(col);
			}
			BigDecimal b = new BigDecimal(amount);  
			amount = b.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();
			r.put("TOTAL", amount);
		}
	
		// 处理合计行
		if(result.size()>0){
			RowBase totalRow = new RowBase();
			totalRow.put("CODE", "合计");
			for (RowBase col : headers) {
				if ("CODE".equals(col.getSafeString("COLUMN")))
					continue;
		
				amount = 0d;
				for (RowBase r : result.values()) {
					amount += r.getSafeDouble(col.getSafeString("COLUMN"));
				}
				BigDecimal b = new BigDecimal(amount);  
				amount = b.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();
				totalRow.put(col.getSafeString("COLUMN"), amount);
			}
			result.put("TOTAL", totalRow);
		}
		return result;
	}

	/**
	 * 获取季度报表表头
	 * @param year
	 * @param quarter
	 * @param isCity
	 * @return
	 */
	public List<RowBase> getQuarterHeaders(String year, String quarter,boolean isCity) {
		List<RowBase> headers = new ArrayList<>();
		RowBase xzqHeader = new RowBase();
		if(isCity){
			xzqHeader.put("COLUMN", "CODE");
			xzqHeader.put("HEADER", "行政区划");
		}else{
			xzqHeader.put("COLUMN", "CODE");
			xzqHeader.put("HEADER", "企业名称");
		}
		headers.add(xzqHeader);
		
		if("1".equals(quarter)){
			headers.addAll(getQuarterOrYearHeader(year, 1,2,3));
		}else if("2".equals(quarter)){
			headers.addAll(getQuarterOrYearHeader(year, 4,5,6));
		}else if("3".equals(quarter)){
			headers.addAll(getQuarterOrYearHeader(year, 7,8,9));
		}else if("4".equals(quarter)){
			headers.addAll(getQuarterOrYearHeader(year, 10,11,12));
		}
		RowBase totalHeader = new RowBase();
		totalHeader.put("COLUMN", "TOTAL");
		totalHeader.put("HEADER", "总计");
		headers.add(totalHeader);
		return headers;
	}
	
	
	/**
	 * 获取季度，年度报表表头
	 * @param year
	 * @param months
	 * @return
	 */
	private List<RowBase> getQuarterOrYearHeader(String year, int... months) {
		List<RowBase> headers = new ArrayList<>();
		// 各个日期的列名和表头
		String key = null;
		for(int m:months){
			key = Integer.toString(m);
			if (m < 10) {
				key = "0" + key;
			}
			key = "COL" + key;

			RowBase rowHeader = new RowBase();
			rowHeader.put("COLUMN", key);
			rowHeader.put("HEADER", m + "月");
			headers.add(rowHeader);
		}
	
		return headers;
	}

	/**
	 * 获取市级季度报表数据
	 * @param year
	 * @param quarter
	 * @param param
	 * @param flowType
	 * @param waterType
	 * @return
	 */
	private List<RowBase> getCityQuarterList(String year, String quarter, String param,String flowType,String waterType) {
	
		int iYear = Integer.parseInt(year);
		int iQuarter = Integer.parseInt(quarter);
		String start = DateHelper.getFirstDayOfQuarter(iYear, iQuarter);
		String end = DateHelper.getLastDayOfQuarter(iYear, iQuarter);
	
		String sql = "select  k.xzq,k.DAYCOL, round(sum(k.cou), 2) as AMOUNT" 
				+ " from (select t.cou,"
				+ "          to_char(t.datatime, 'mm') as DAYCOL," 
				+ "          substr(dci.site_id, 3, 2) as XZQ"
				+ "     from monitor_data_day t"
				+ "     left join BC_COLLECTION_INFO dci on t.mn = dci.device_num" 
				+ "     left join BC_SITE_INFO ms on dci.site_id = ms.code"
				+ "    where t.parmname='"	+ param + "'" ;
		if(StringUtil.IsNotNullOrEmpty(flowType)){
			sql+= "      and ms.flow_type = '"+flowType+"'" ;
		}
		if(StringUtil.IsNotNullOrEmpty(waterType)){
			sql+= "      and ms.STATION_CATEGORY = '"+waterType+"'" ;
		}
		sql+= "      and t.datatime >= to_date('" + start + " 00:00:00', 'yyyy-MM-dd HH24:MI:SS')"
				+ "      and t.datatime <= to_date('" + end + " 23:59:59', 'yyyy-MM-dd HH24:MI:SS')"
				+ "    order by t.datatime) k" + "  group by k.daycol, k.xzq order by k.xzq,k.daycol";
	
		List<RowBase> listData = DataUtil.getSelectRows(sql, null);
		return listData;
	}

	/**
	 * 获取区域季度报表
	 * @param region
	 * @param year
	 * @param quarter
	 * @param param
	 * @param flowType
	 * @param waterType
	 * @return
	 */
	public Map<String, RowBase> generateRegionQuarterReport(String region, String year, String quarter, String param,String flowType,String waterType) {
		List<RowBase> listData = getRegionQuarterList(region, year, quarter, param,flowType,waterType);
		// 生成表头
		List<RowBase> headers = getQuarterHeaders(year, quarter,false);
	
		String wwtpCode = "";
		Map<String, RowBase> result = new LinkedHashMap<String, RowBase>();
		RowBase row = null;
		String day = null;
		double amount = 0d;
		for (RowBase r : listData) {
			wwtpCode = r.getSafeString("WWTPCODE");
			if (result.containsKey(wwtpCode)) {
				row = result.get(wwtpCode);
			} else {
				row = new RowBase();
				for (RowBase col : headers) {
					row.put(col.getSafeString("COLUMN"), null);
				}
				row.put("CODE", r.getSafeString("WWTPNAME"));
				result.put(wwtpCode, row);
			}
	
			day = r.getSafeString("DAYCOL");
			amount = r.getSafeDouble("AMOUNT");
			row.put("COL" + day, amount);
		}
	
		// 处理合计列
		for (RowBase r : result.values()) {
			amount = 0d;
			for (String col : r.keySet()) {
				if ("TOTAL".equals(col) || RowBase.FIELD_ISNEW.equals(col))
					continue;
	
				amount += r.getSafeDouble(col);
			}
			BigDecimal b = new BigDecimal(amount);  
			amount = b.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();
			r.put("TOTAL", amount);
		}
	
		// 处理合计行
		if(result.size()>0){
			RowBase totalRow = new RowBase();
			totalRow.put("CODE", "合计");
			for (RowBase col : headers) {
				if ("CODE".equals(col.getSafeString("COLUMN")))
					continue;
		
				amount = 0d;
				for (RowBase r : result.values()) {
					amount += r.getSafeDouble(col.getSafeString("COLUMN"));
				}
				BigDecimal b = new BigDecimal(amount);  
				amount = b.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();
				totalRow.put(col.getSafeString("COLUMN"), amount);
			}
			result.put("TOTAL", totalRow);
		}
		return result;
	}

	/**
	 * 获取区域年度数据列表
	 * @param region
	 * @param year
	 * @param param
	 * @param flowType
	 * @param waterType
	 * @return
	 */
	private List<RowBase> getRegionYearList(String region, String year, String param,String flowType,String waterType) {
	
		int iYear = Integer.parseInt(year);
		String start = DateHelper.getFirstDayOfMonth(iYear,1);
		String end = DateHelper.getFirstDayOfMonth(iYear,12);
	
		String sql = "select k.WWTPCODE,k.WWTPNAME,k.DAYCOL, round(sum(k.cou), 2) as AMOUNT"
				+ " from (select to_char(t.datatime, 'mm') as DAYCOL,"
				+ "             ms.code as STATIONCODE,ms.name as STATIONNAME,"
				+ "             p.code as WWTPCODE,p.name as WWTPNAME,t.cou" 
				+ "        from monitor_data_day t"
				+ "        left join BC_COLLECTION_INFO dci on t.mn = dci.device_num"
				+ "       left join BC_SITE_INFO ms on dci.site_id = ms.code"
				+ "       left join BC_ENTERPRISE_ATTR p on ms.wwtp_id = p.code" 
				+ "      where t.parmname='"+param+"'";
		
		if(StringUtil.IsNotNullOrEmpty(flowType)){
			sql+= "      and ms.flow_type = '"+flowType+"'" ;
		}
		if(StringUtil.IsNotNullOrEmpty(waterType)){
			sql+= "      and ms.STATION_CATEGORY = '"+waterType+"'" ;
		}
				
		sql+= "      and t.datatime >= to_date('" + start + " 00:00:00', 'yyyy-MM-dd HH24:MI:SS')" 
				+ "      and t.datatime <= to_date('" + end	+ " 23:59:59', 'yyyy-MM-dd HH24:MI:SS')" 
				+ "       and dci.site_id like '__" + region + "%'"
				+ "       order by t.datatime) k"
				+ " group by k.WWTPCODE,k.WWTPNAME,k.daycol order by k.WWTPCODE,k.WWTPNAME,k.daycol";
	
		List<RowBase> listData = DataUtil.getSelectRows(sql, null);
		return listData;
	}

	/**
	 * 获取市级年度报表
	 * @param year
	 * @param param
	 * @param flowType
	 * @param waterType
	 * @return
	 */
	public Map<String, RowBase> generateCityYearReport(String year, String param,String flowType,String waterType) {
		
		List<RowBase> listData = getCityYearList(year, param,flowType,waterType);
		
		// 生成表头
		List<RowBase> headers = getYearHeaders(year, true);
	
		String xzq = "";
		List<RowBase> xzqhs = domainService.getXZQHDomain();
		Map<String, String> xzqMap = new HashMap<>();
		for (RowBase r : xzqhs) {
			xzq = r.getSafeString("S_DOMAINCODE");
			xzqMap.put(xzq, r.getSafeString("S_DOMAINCAPTION"));
		}
	
		Map<String, RowBase> result = new LinkedHashMap<String, RowBase>();
		RowBase row = null;
		String day = null;
		double amount = 0d;
		for (RowBase r : listData) {
			xzq = r.getSafeString("XZQ");
			if (result.containsKey(xzq)) {
				row = result.get(xzq);
			} else {
				row = new RowBase();
				for (RowBase col : headers) {
					row.put(col.getSafeString("COLUMN"), null);
				}
				row.put("CODE", xzqMap.get(xzq) == null ? xzq : xzqMap.get(xzq));
				result.put(xzq, row);
			}
	
			day = r.getSafeString("DAYCOL");
			amount = r.getSafeDouble("AMOUNT");
			row.put("COL" + day, amount);
		}
	
		// 处理合计列
		for (RowBase r : result.values()) {
			amount = 0d;
			for (String col : r.keySet()) {
				if ("TOTAL".equals(col) || RowBase.FIELD_ISNEW.equals(col))
					continue;
	
				amount += r.getSafeDouble(col);
			}
			BigDecimal b = new BigDecimal(amount);  
			amount = b.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();
			r.put("TOTAL", amount);
		}
	
		// 处理合计行
		if(result.size()>0){
			RowBase totalRow = new RowBase();
			totalRow.put("CODE", "合计");
			for (RowBase col : headers) {
				if ("CODE".equals(col.getSafeString("COLUMN")))
					continue;
		
				amount = 0d;
				for (RowBase r : result.values()) {
					amount += r.getSafeDouble(col.getSafeString("COLUMN"));
				}
				BigDecimal b = new BigDecimal(amount);  
				amount = b.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();
				totalRow.put(col.getSafeString("COLUMN"), amount);
			}
			result.put("TOTAL", totalRow);
		}
		return result;
	}

	/**
	 * 获取年度报表表头
	 * @param year
	 * @param isCity
	 * @return
	 */
	public List<RowBase> getYearHeaders(String year, boolean isCity) {
		List<RowBase> headers = new ArrayList<>();
		RowBase xzqHeader = new RowBase();
		if(isCity){
			xzqHeader.put("COLUMN", "CODE");
			xzqHeader.put("HEADER", "行政区划");
		}else{
			xzqHeader.put("COLUMN", "CODE");
			xzqHeader.put("HEADER", "企业名称");
		}
		headers.add(xzqHeader);
		
		headers.addAll(getQuarterOrYearHeader(year, new int[]{1,2,3,4,5,6,7,8,9,10,11,12}));
		
		RowBase totalHeader = new RowBase();
		totalHeader.put("COLUMN", "TOTAL");
		totalHeader.put("HEADER", "总计");
		headers.add(totalHeader);
		return headers;
	}

	/**
	 * 获取市级年度数据
	 * @param year
	 * @param param
	 * @param flowType
	 * @param waterType
	 * @return
	 */
	private List<RowBase> getCityYearList(String year, String param,String flowType,String waterType) {
	
		int iYear = Integer.parseInt(year);
		String start = DateHelper.getFirstDayOfMonth(iYear, 1);
		String end = DateHelper.getLastDayOfMonth(iYear, 12);
	
		String sql = "select  k.xzq,k.DAYCOL, round(sum(k.cou), 2) as AMOUNT" 
				+ " from (select t.cou,"
				+ "          to_char(t.datatime, 'mm') as DAYCOL," 
				+ "          substr(dci.site_id, 3, 2) as XZQ"
				+ "     from monitor_data_day t"
				+ "     left join BC_COLLECTION_INFO dci on t.mn = dci.device_num" 
				+ "     left join BC_SITE_INFO ms on dci.site_id = ms.code"
				+ "    where t.parmname='"	+ param + "'" ;
		
		if(StringUtil.IsNotNullOrEmpty(flowType)){
			sql+= "      and ms.flow_type = '"+flowType+"'" ;
		}
		if(StringUtil.IsNotNullOrEmpty(waterType)){
			sql+= "      and ms.STATION_CATEGORY = '"+waterType+"'" ;
		}
		
		sql+= "      and t.datatime >= to_date('" + start + " 00:00:00', 'yyyy-MM-dd HH24:MI:SS')"
				+ "      and t.datatime <= to_date('" + end + " 23:59:59', 'yyyy-MM-dd HH24:MI:SS')"
				+ "    order by t.datatime) k" + "  group by k.daycol, k.xzq order by k.xzq,k.daycol";
	
		List<RowBase> listData = DataUtil.getSelectRows(sql, null);
		return listData;
	}

	/**
	 * 获取区域年度报表
	 * @param region
	 * @param year
	 * @param param
	 * @param flowType
	 * @param waterType
	 * @return
	 */
	public Map<String, RowBase> generateRegionYearReport(String region, String year, String param,String flowType,String waterType) {
		List<RowBase> listData = getRegionYearList(region, year, param,flowType,waterType);
		// 生成表头
		List<RowBase> headers = getYearHeaders(year, false);
	
		String wwtpCode = "";
		Map<String, RowBase> result = new LinkedHashMap<String, RowBase>();
		RowBase row = null;
		String day = null;
		double amount = 0d;
		for (RowBase r : listData) {
			wwtpCode = r.getSafeString("WWTPCODE");
			if (result.containsKey(wwtpCode)) {
				row = result.get(wwtpCode);
			} else {
				row = new RowBase();
				for (RowBase col : headers) {
					row.put(col.getSafeString("COLUMN"), null);
				}
				row.put("CODE", r.getSafeString("WWTPNAME"));
				result.put(wwtpCode, row);
			}
	
			day = r.getSafeString("DAYCOL");
			amount = r.getSafeDouble("AMOUNT");
			row.put("COL" + day, amount);
		}
	
		// 处理合计列
		for (RowBase r : result.values()) {
			amount = 0d;
			for (String col : r.keySet()) {
				if ("TOTAL".equals(col) || RowBase.FIELD_ISNEW.equals(col))
					continue;
	
				amount += r.getSafeDouble(col);
			}
			BigDecimal b = new BigDecimal(amount);  
			amount = b.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();
			r.put("TOTAL", amount);
		}
	
		// 处理合计行
		if(result.size()>0){
			RowBase totalRow = new RowBase();
			totalRow.put("CODE", "合计");
			for (RowBase col : headers) {
				if ("CODE".equals(col.getSafeString("COLUMN")))
					continue;
		
				amount = 0d;
				for (RowBase r : result.values()) {
					amount += r.getSafeDouble(col.getSafeString("COLUMN"));
				}
				BigDecimal b = new BigDecimal(amount);  
				amount = b.setScale(2,   BigDecimal.ROUND_HALF_UP).doubleValue();
				totalRow.put(col.getSafeString("COLUMN"), amount);
			}
			result.put("TOTAL", totalRow);
		}
		return result;
	}

	/**
	 * 获取区域季度数据
	 * @param region
	 * @param year
	 * @param quarter
	 * @param param
	 * @param flowType
	 * @param waterType
	 * @return
	 */
	private List<RowBase> getRegionQuarterList(String region, String year, String quarter, String param,String flowType,String waterType) {
	
		int iYear = Integer.parseInt(year);
		int iQuarter = Integer.parseInt(quarter);
		String start = DateHelper.getFirstDayOfQuarter(iYear, iQuarter);
		String end = DateHelper.getFirstDayOfQuarter(iYear, iQuarter);
	
		String sql = "select k.WWTPCODE,k.WWTPNAME,k.DAYCOL, round(sum(k.cou), 2) as AMOUNT"
				+ " from (select to_char(t.datatime, 'mm') as DAYCOL,"
				+ "             ms.code as STATIONCODE,ms.name as STATIONNAME,"
				+ "             p.code as WWTPCODE,p.name as WWTPNAME,t.cou" 
				+ "        from monitor_data_day t"
				+ "        left join BC_COLLECTION_INFO dci on t.mn = dci.device_num"
				+ "       left join BC_SITE_INFO ms on dci.site_id = ms.code"
				+ "       left join BC_ENTERPRISE_ATTR p on ms.wwtp_id = p.code" 
				+ "      where t.parmname='"+param+"'";
		
		if(StringUtil.IsNotNullOrEmpty(flowType)){
			sql+= "      and ms.flow_type = '"+flowType+"'" ;
		}
		if(StringUtil.IsNotNullOrEmpty(waterType)){
			sql+= "      and ms.STATION_CATEGORY = '"+waterType+"'" ;
		}
				
		sql+= "      and t.datatime >= to_date('" + start + " 00:00:00', 'yyyy-MM-dd HH24:MI:SS')" 
				+ "      and t.datatime <= to_date('" + end	+ " 23:59:59', 'yyyy-MM-dd HH24:MI:SS')" 
				+ "       and dci.site_id like '__" + region + "%'"
				+ "       order by t.datatime) k"
				+ " group by k.WWTPCODE,k.WWTPNAME,k.daycol order by k.WWTPCODE,k.WWTPNAME,k.daycol";
	
		List<RowBase> listData = DataUtil.getSelectRows(sql, null);
		return listData;
	}
	
	/**
	 * 获取水质标准
	 * @param params
	 * @return
	 */
	public List<RowBase> getWaterQualityStandards(Map<String,String> params){
		String sql = "select * from BC_QUALITY_STANDARD t where 1=1";
		
		List<GlobalParameter> listParams = new ArrayList<>();
		if(params.containsKey("category")){
			sql += " and t.category=@category";
			listParams.add(new GlobalParameter("category",params.get("category")));
		}
		if(params.containsKey("item")){
			sql += " and t.item=@item";
			listParams.add(new GlobalParameter("item",params.get("item")));
		}
		
		return DataUtil.getSelectRows(sql, listParams);
	}
	/**
	 * 四舍六入5考虑
	 * @param value
	 * @param digit,小数位
	 * @return
	 */
	  public String setDataScale(double value, int digit){
		  String result = "";
		  try {
			  double ratio = Math.pow(10, digit);
			  
		      BigDecimal a1 = new BigDecimal(Double.toString(ratio));  
		      BigDecimal b1 = new BigDecimal(Double.toString(value));   
		      BigDecimal data = a1.multiply(b1);
				  
			  double _num = data.doubleValue();
			  double mod = _num % 1;
			  double integer = Math.floor(_num);
			  double returnNum;
			  if(mod > 0.5){
				  returnNum=(integer + 1) / ratio;    
				} else if (mod < 0.5) {
					  returnNum=integer / ratio;    
				} else {
					  returnNum=(integer % 2 == 0 ? integer : integer + 1) / ratio;    
				}
			  BigDecimal bg = new BigDecimal(returnNum); 
			  result = bg.setScale((int)digit, BigDecimal.ROUND_HALF_UP).toString();
		  } catch (RuntimeException e) {
			  throw e;
			  }
		  return result;
		  }
	  
		/**
		 * 获取浓度历史数据
		 * @param params 
		 *  wwtp_id = params.get("wwtp_id") <br>
		 *  flow_type = params.get("flow_type") <br>
			station_id = params.get("station_id") <br>
			start = params.get("start") <br>
			end = params.get("end") <br>
			dataSource = params.get("dataSource") <br>
			parmToChecks = params.get("parmToChecks") <br>
		 * @param pageInfo 分页信息，为空的话，不分页
		 * @param isForMondifyCation 是否修约模式的查询
		 * @return
		 * @throws Exception
		 */
		public List<RowBase> getIndustHistoricalData(Map<String,String> params,PageInfo pageInfo) throws Exception {
			
			String wwtp_id = params.get("wwtp_id");
			String start = params.get("start");
			String end = params.get("end");
			String dataSource = params.get("dataSource");
			String parmToChecks = params.get("parmToChecks");
			String mn = params.get("mn");
			String waterType = RequestUnit.getParameter("WATER_TYPE");		
			String[] parms = parmToChecks.split(",");
			
			String getDataSql = "";
			if("5".equals(dataSource)){
				getDataSql = getAmountSql(wwtp_id,parms,start,end);
			}
			else{
				getDataSql = getIndustHistoricalDataSql(mn, parms, start, end, dataSource);			
			}
			List<RowBase> list = null;
			
			ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
			ITable table = tableFactory.openTable("MONITOR_DATA");
			
			if (pageInfo==null) {
				list = DataUtil.getSelectRows(getDataSql, null, table);
			} else {
				list = DataUtil.getPageRow(getDataSql, null, table,pageInfo);
			}
			
			list = NumPointFormat(list, parms,waterType);
			list = GenerateAlarmInfo(list, parms, mn);
			
			return list;
		}
		/**
		 * 熊伟2017/2/28
		 * 
		 * @param mn 站点mn号
		 * @param parms，查询参数
		 * @param start，开始时间
		 * @param end，结束时间
		 * @param dataSource，小时，分钟，还是日均值
		 * @return
		 * @throws ParseException
		 */
		
		private String getIndustHistoricalDataSql(String mn, String[] parms, String start, String end,
				String dataSource) throws ParseException {

			int iDataSource = Integer.parseInt(dataSource);

			String tableName = "monitor_data";
			switch (iDataSource) {
			case 2:
				tableName = "monitor_data_min";
				break;
			case 3:
				tableName = "monitor_data_hour";
				break;
			case 4:
				tableName = "monitor_data_day";
				break;
			case 6:
				tableName = "monitor_data_cycle";
				break;
			case 7:
				tableName = "MONITOR_DATA_DAY_CONFIGSOFT ";
				break;
			case 8:
				tableName = "MONITOR_DATA_HOUR_CONFIGSOFT";
				break;
			}
			
			String parmAvgItems = "";
			String parmZSItems = "";
			String parmFlagItems = "";
			
			for(String parmName : parms){
				parmAvgItems += ",'"+parmName+"' P"+parmName+"AVG";
				parmZSItems += ",'"+parmName+"' P"+parmName+"ZS_AVG";
				parmFlagItems += ",'"+parmName+"' P"+parmName+"FLAG";
			}
			parmAvgItems = parmAvgItems.replaceFirst(",", "");
			parmZSItems = parmZSItems.replaceFirst(",", "");
			parmFlagItems = parmFlagItems.replaceFirst(",", "");
			//查询实测数据SQL
			String realDataSql ="";
			String ZSDataSql ="";
			String queryDataSql="";
			if(iDataSource == 4){
				realDataSql = "select * from (\n"+"SELECT * FROM (\n" +
						"       select * from ( select to_char(datatime,'hh24') hour,to_char(datatime,'dd') day,mn,to_char(datatime,'yyyy-mm-dd') datatime,PARMNAME,avg,QN,DATA_FROM,MODIFY_CERTIFICATE_ID from "+tableName+" t1 \n"
						+ "			where datatime >= to_date('" + start + "','YYYY-MM-DD HH24:MI:SS') AND datatime <= to_date('" + end + "','YYYY-MM-DD HH24:MI:SS') AND MN = '"+mn+"' and  PARMNAME!='COMP_POLL_INDEX' AND t1.FLAG = 'N'  ) t\n" + 
						"              pivot(avg(avg) for PARMNAME in ("+parmAvgItems+"))\n" + 
						"       ) TAVG\n"
						;
				ZSDataSql = "SELECT * FROM (\n" +
						"       select * from ( select to_char(datatime,'hh24') hour,to_char(datatime,'dd') day,mn,to_char(datatime,'yyyy-mm-dd') datatime,PARMNAME,ZS_AVG,QN,DATA_FROM,MODIFY_CERTIFICATE_ID from "+tableName+" t1 \n"
						+ "			where datatime >= to_date('" + start + "','YYYY-MM-DD HH24:MI:SS') AND datatime <= to_date('" + end + "','YYYY-MM-DD HH24:MI:SS') AND MN = '"+mn+"' and  PARMNAME!='COMP_POLL_INDEX' AND t1.FLAG = 'N'  ) t\n" + 
						"              pivot(avg(ZS_AVG) for PARMNAME in ("+parmZSItems+"))\n" + 
						"       ) TAVG\n"
						;	
			}
			else{
				realDataSql = "select * from (\n"+"SELECT * FROM (\n" +
						"       select * from ( select to_char(datatime,'hh24') hour,to_char(datatime,'dd') day,mn,datatime,PARMNAME,avg,QN,DATA_FROM,MODIFY_CERTIFICATE_ID from "+tableName+" t1 \n"
						+ "			where datatime >= to_date('" + start + "','YYYY-MM-DD HH24:MI:SS') AND datatime <= to_date('" + end + "','YYYY-MM-DD HH24:MI:SS') AND MN = '"+mn+"' and  PARMNAME!='COMP_POLL_INDEX' AND t1.FLAG = 'N'  ) t\n" + 
						"              pivot(avg(avg) for PARMNAME in ("+parmAvgItems+"))\n" + 
						"       ) TAVG\n"
						;	
				ZSDataSql = "SELECT * FROM (\n" +
						"       select * from ( select to_char(datatime,'hh24') hour,to_char(datatime,'dd') day,mn,datatime,PARMNAME,ZS_AVG,QN,DATA_FROM,MODIFY_CERTIFICATE_ID from "+tableName+" t1 \n"
						+ "			where datatime >= to_date('" + start + "','YYYY-MM-DD HH24:MI:SS') AND datatime <= to_date('" + end + "','YYYY-MM-DD HH24:MI:SS') AND MN = '"+mn+"' and  PARMNAME!='COMP_POLL_INDEX' AND t1.FLAG = 'N'  ) t\n" + 
						"              pivot(avg(ZS_AVG) for PARMNAME in ("+parmZSItems+"))\n" + 
						"       ) TAVG\n"
						;
			}
			realDataSql +=" order by TAVG.DATATIME DESC"+") t1\n"+"left join (";
			ZSDataSql +=" order by TAVG.DATATIME DESC"+") t2\n";
			queryDataSql = realDataSql+ZSDataSql+"on t1.mn=t2.mn and t1.datatime=t2.datatime";

			return queryDataSql;
		}
		/**
		 * 获取工业污染源实时数据（包括实测数据和折算数据）
		 * @param params
		 * @param pageInfo
		 * @return
		 * @throws Exception
		 */
		public ListPageResult getIndustryPolluteRealTimeData(Map<String,String> params,PageInfo pageInfo) throws Exception {

			String monitorEquipmentNo = params.get("mn");
			String waterType = params.get("waterType");
			if(monitorEquipmentNo == null ||monitorEquipmentNo.equals("")){
				return null;
			}
			String parmItems = "";
			String parmZSItems = "";
			String timeWhere = "";
			
			String parmToChecks = params.get("parmToChecks");
			String[] parms = parmToChecks.split(",");
			
			
			for(String parmName : parms){
				parmItems += ",'"+parmName+"' P"+parmName+"AVG";
				parmZSItems+= ",'"+parmName+"' P"+parmName+"ZS_RTD";
			}
			parmItems = parmItems.replaceFirst(",", "");
			parmZSItems = parmZSItems.replaceFirst(",", "");
			
			if(params.containsKey("start") && params.containsKey("end")){
				timeWhere = " datatime>=to_date('" + params.get("start") + "','YYYY-MM-DD HH24:MI:SS') and  datatime<to_date('" + params.get("end") + "','YYYY-MM-DD HH24:MI:SS') ";
			}
			else{
				timeWhere = "datatime > sysdate - 1/24 * 0.5";
			}

			String RTDsql = "SELECT * FROM ( select * from ( select datatime,PARMNAME,rtd,mn from monitor_data where 1=1 and "+timeWhere+" AND mn = @mn and flag = 'N' ORDER BY DATATIME) t \n"
					+ " pivot(avg(rtd) for PARMNAME in ("+parmItems+"))\n"
							+ " order by datatime desc ) t1  ";
			
			String ZS_RTDsql = "select * from ( select datatime,PARMNAME,ZS_RTD,mn from monitor_data where 1=1 and "+timeWhere+" AND mn = @mn and flag = 'N' ORDER BY DATATIME) t \n"
					+ " pivot(avg(ZS_RTD) for PARMNAME in ("+parmZSItems+"))\n"
							+ " order by datatime desc ) t2  ";
			
			String queryRtdDataSql = RTDsql+"\n"+"left join (\n"+ZS_RTDsql+"on t1.mn=t2.mn and t1.datatime=t2.datatime ";
			
			
			List<GlobalParameter> pc = new ArrayList<>();
			pc.add(new GlobalParameter("mn", monitorEquipmentNo));
			
			ListPageResult result = DataUtil.getPageListResult(queryRtdDataSql, pc,null);
			List<RowBase> list = result.getList();
			list = NumPointFormat(list,parms,waterType);//格式化小数点
			list = GenerateAlarmInfo(list, parms, monitorEquipmentNo);//生成报警信息
			
			result.setList(list);
			
			return result;
		}
}
